Change column datatype from String to Float in Postgres. Error

Question

I have two columns of latitude and longitude in a table with the column datatypes as string. There also some entries which are 'blank' but I think saved as a "" string.

I have had a look through the some of the similar questions here but I can't find an exact answer.

When I try to alter the column datatype I get the following error.

ALTER TABLE listings ALTER COLUMN latitude TYPE FLOAT USING latitude::float;                                                                                                                                
ERROR:  invalid input syntax for type double precision: ""

I think this is because the blanks/nulls are being considered as a string "".

Whats the best way to deal with this?

Thanks

No it is because there are empty strings in the fields. If they where NULL's then you would not be seeing the problem. Other databases may consider '' IS NULL, Postgres does not. — Adrian Klaver
– Adrian Klaver, Commented Jul 7, 2020 at 0:01

Gordon Linoff · Accepted Answer · 2020-07-06 23:27:13Z

4

Something like this:

ALTER TABLE listings ALTER COLUMN latitude TYPE FLOAT
    USING (CASE WHEN latitude = '' THEN 0.0 ELSE latitude::float END);

If you still have problems with bad characters, you can try:

ALTER TABLE listings ALTER COLUMN latitude TYPE FLOAT
    USING (CASE WHEN latitude = '' THEN 0.0 ELSE REGEXP_REPLACE(latitude, '[^-0-9.]', '', 'g') END);

This removes all non-digit like characters so the conversion should go ahead. Note that the value could still be a non-float (e.g. '1.2.3'), but that seems unlikely for the column.

Also, you might want to consider a numeric instead of float; fixed point arithmetic makes sense for geographic coordinates.

answered Jul 6, 2020 at 23:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Laurenz Albe Over a year ago

Correct answer, but double precision is the data type for geographical coordinates.

rtrt1 Over a year ago

Worked beautifully. Thanks a lot Gordon! The correct data type for geographic coordinates is probably a discussion for a new question!

Mike Organek · Accepted Answer · 2020-07-06 23:09:24Z

0

I have no idea whether this is the best plan, but this is what I would do:

select count(*) from 
  (select (case 
             when nullif(latitude, '') is null then null
             else latitude
           end)::float
     from listings
  ) x;

If that blows up, then I would look at why it is blowing up and then add the new exclusion candidate to the case.

Once I find all the pathological cases, I would:

update listings 
   set latitude = case
                    when latitude = `` then null
                    when latitude = 'some other messed-up representation' then null
                    else latitude
                  end
;

answered Jul 6, 2020 at 23:09

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

1 Comment

rtrt1 Over a year ago

Thanks, this would have got me there as well I think and is probably more complete if there were more other representations in the data.

Collectives™ on Stack Overflow

Change column datatype from String to Float in Postgres. Error

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related