Postgres columns of latitude and longitude from varchar to numeric

Question

I have a table with latitude and longitude columns and they are in varchar. I have to convert them to numeric because that is how the fields are in the master table.

Is there a way to change all the values in the columns at one time from varchar to numeric in PostgreSQL?

I am struggling because since they are lat/long values they vary in length, have decimals and the longitude is negative.

Table Name

Latitude | Longitude
+-----------------------------+
35.0528620000 | -119.375136000
+-------------|---------------+

and they go on like this. I have tried ALTER TABLE, but that just gives me an an error, CAN NOT BE CAST and I tried http://www.postgresql.org/docs/9.3/static/functions-formatting.html to_char

EDIT: I was able to get the fields almost to the same length. Latitude is not 13 char and Longitude is 14 including the negative sign.

EDIT: The alter table error in Navicat:

[Err] ERROR: syntax error at or near "ALTER"
LINE 1: EXPLAIN ALTER TABLE "allwells_CA_copy"

In Postgres:

ERROR: syntax error at or near "ALTER" LINE 1: ..., VERBOSE off, COSTS on, BUFFERS off, TIMING off )ALTER TABL... ^ ********** Error ********** ERROR: syntax error at or near "ALTER" SQL state: 42601 Character: 71

I've been switching between PG Admin and Navicat in an attempt to make the change.

• Craig Ringer: Always include the exact text of any error message and where possible the SQL that produced it.
• T.J.: Here is the original ALTER table error. I tried so many things yesterday with so many errors. I made an edit above
• Craig Ringer: and the SQL that produced that error?

Answer 1

You need the USING clause to ALTER TABLE ... TYPE ..., e.g.:

ALTER TABLE mytable
 ALTER COLUMN "Longitude" TYPE NUMERIC(14, 11) 
 USING ("Longditude"::NUMERIC(14,11)),
 ALTER COLUMN "Latitude" TYPE NUMERIC(14, 11) 
 USING ("Latitude"::NUMERIC(14,11));

assuming you want a numeric with precision 14 and scale 11 and your columns really do have an upper case first letter.

For more details see the manual on ALTER TABLE.

after edit:

You seem to be using a broken client that's trying to prepend an EXPLAIN to the ALTER TABLE. You can't EXPLAIN an ALTER TABLE statement.

• T.J.: I wrote something similar to this but I keep getting an error right at the 'ALTER' line 1. With your code the error is: ERROR: syntax error at or near "ALTER" LINE 1: ..., VERBOSE off, COSTS on, BUFFERS off, TIMING off )ALTER TABL... ^ ********** Error ********** ERROR: syntax error at or near "ALTER" SQL state: 42601 Character: 71
• Craig Ringer: You appear to be attempting to EXPLAIN ANALYZE an ALTER TABLE. You can't EXPLAIN an ALTER TABLE. Just run it without the EXPLAIN (...).

Answer 2

GIS

You shouldn't be storing lat/long as numeric, nor varchar. Use PostGIS and store them as a GEOGRAPHY. This allows you to do intersection tests on an index, find all points within X-range, aggregate into lines, or polygons -- and a lot more.

CREATE EXTENSION postgis;
BEGIN;
 ALTER TABLE foo ADD COLUMN geog geography;
 UPDATE foo SET geog = ST_MakePoint(long, lat);
 CREATE INDEX on foo USING gist ( geog );
 ALTER TABLE foo DROP COLUMN long, DROP COLUMN lat;
COMMIT;