3

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.

3
  • 1
    Always include the exact text of any error message and where possible the SQL that produced it. Oct 7 '14 at 13:45
  • Here is the original ALTER table error. I tried so many things yesterday with so many errors. I made an edit above
    – T.J.
    Oct 7 '14 at 14:07
  • and the SQL that produced that error? Oct 7 '14 at 14:22
4

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.

2
  • 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
    – T.J.
    Oct 7 '14 at 14:11
  • You appear to be attempting to EXPLAIN ANALYZE an ALTER TABLE. You can't EXPLAIN an ALTER TABLE. Just run it without the EXPLAIN (...). Oct 7 '14 at 14:21
0

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;

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.