William William - 5 months ago 11
SQL Question

Postgres: Why do I get an input syntax error when modifying a char column into a timestamp?

I copied data from a csv to my table. However, the timestamp data was copied with the column on the table as char(40). Now I want to modify the table to have it as a timestamp but keep getting an error specifying input syntax error.

The data is formatted in ISO8601 I believe

e.g. 2016-06-03T08:00:00.020584124-04:00

I've tried both of these SQL statements with the same result

ALTER TABLE public.data
ALTER COLUMN timestamp TYPE TIMESTAMP WITH TIME ZONE USING timestamp::TIMESTAMP WITH TIME ZONE;

ALTER TABLE public.data
ALTER COLUMN timestamp TYPE TIMESTAMPTZ USING timestamp::TIMESTAMPTZ;


UPDATE: Turns out when I used psycopg2's copy_from, it read the header values. That explains why I had problems trying to change any of the values that were char(). Once I used copy_expert and specified that the csv contained headers, I was able to use the TIMESTAMPTZ data type.

Answer

Your ALTER statements are both ok. You believe the values are in ISO8601, but there are some that are not. You can try to find them using regexp, e.g.:

select *
from public.data
where regexp_replace (timestamp, '\d{4}-\d\d-\d\dT\d\d:\d\d:\d\d\.\d{9}-\d\d:\d\d', '') <> ''

Btw, it is a bad idea to have timestamp as a column name.