Postgres casting between varchar and double precision suddenly stopped working

Question

I have been importing data into PostgreSQL tables all day using ogr2ogr and then spatially enabling the data with no issues up to now. I have been cutting and pasting the code and this is what I have been using:

ALTER TABLE mytable ADD COLUMN geom geometry(POINT,4326);
UPDATE mytable SET geom = ST_SetSRID(ST_MakePoint(cast (lon AS double precision),cast (lat AS double precision ) ),4326);

On the last table, I suddenly get this error:

ERROR:  invalid input syntax for type double precision: "#VALUE!"

Nothing has changed, at least as far as I know, I was importing a csv file, same as all the others.

I checked the csv file for spaces or unusual characters but it looks in order

Column lon, lon are of type character varying

here is a sample of the data

id,name,name_alt,lat,lon,description
1,Abbott Seamount,,31.8,174.3,A seamount located north of Midway Islands (US) in the North Pacific Ocean; name origin unknown.
2,Abra Canyon,,17.51667,120.36667,An undersea canyon located off the northwest coast of the Philippine island of Luzon in the South China Sea; named after the Abra River
3,Abraham Canyon,Abraham Sea Valley,52.61667,172.33333,An undersea canyon in the area of the Aleutian Islands in the North Pacific Ocean; named in association with Abraham Bay; Attu Island; Alaska (US). 
4,Abrolhos Bank,,-18.5,38.75,A bank located off the coast of Brazil in the Atlantic Ocean; upon which sits the Arquipélago dos Abrolhos (archipelago). 
5,Abrolhos Seamounts,Abrolhos Ridge; Cadeia dos Abrolhos,-17.33333,36.5,Seamounts located off the coast of Brazil in the Atlantic Ocean; named in association with the Abrolhos Bank and Arquipélago dos Abrolhos (archipelago). 
6,Abubacer Ridge,,36.8,1.65,An undersea ridge located off the coast of Spain in the western Mediterranean Sea; named for Ibn Tufayl (known as Abubacer to Europeans); 12th-century Spanish-Arab philosopher and physician. 
7,Aby Canyon,,4.25,-3.66667,An undersea canyon located off the coast of Côte d'Ivoire; named in association with Lagune Aby; Côte d'Ivoire (lagoon)

What am I missing here?

Answer 1

For future users:

This problem was caused by bad data. There was the "#" Character on one cell of the csv that I missed that just happened to be the word "#VALUE!" (actually generated by someones formula at one stage I would guess)

The error:

ERROR:  invalid input syntax for type double precision: "#VALUE!"

was being reported by PostgreSQL as that exactly what it was seeing in the data, which was invalid. I had a lot of rows and just missed it.

Moral of the story, check your data carefully. If preparing CSV files for uploading and later GeoSpatial uses, sort on the lat and lon columns, that will show any non numeric values in there quickly.