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.
Best Answer
You need the
USING
clause toALTER TABLE ... TYPE ...
, e.g.: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 theALTER TABLE
. You can'tEXPLAIN
anALTER TABLE
statement.