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
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.
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
;