postgres: Converting '' into NULLS I'm trying to run this query to change my latitude coordinates, which is saved as  text into a double precision

ALTER TABLE mapdata ALTER COLUMN lat TYPE double precision USING (lat::double precision);


However, I keep getting this error: invalid input syntax for type double precision: ""

My assumption is that there is a blank entry and that postgres doesn't know to treat it as a double precision. How would I make this out to be a NULL or is there another way I can approach this to turn my lat column into a double precision? Thanks!
You have two options:


you change the empty strings in the original data to NULLs and then change the type
you handle them while changing the type


The first one would be a simple

UPDATE table SET col=NULL where col='';


but this naturally takes time and is a bit unnecessary since you're changing the type after that.

Second way is to handle the empty strings while converting

ALTER TABLE mapdata
  ALTER COLUMN lat TYPE double precision
    USING (NULLIF(lat, '')::double precision);


The NULLIF will return NULL if the lat column is an empty string, otherwise it'll return the lat column value. This will be a lot more efficient than to first change the source data.