1

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!

  • 3
    Have you tried UPDATE mapdata SET lat = NULL WHERE lat = ""; – Vérace Jul 27 '15 at 16:48
3

You have two options:

  1. you change the empty strings in the original data to NULLs and then change the type
  2. 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.