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.

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.

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;