I'm using PostgreSQL with Python, although the subject matter of the current question seems to be pure SQL.
Based on the PostgreSQL documentation, changing the type of a column should be done like this:
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
So, if I have a table called "checks", in which there is a column "check_date_time" with the type "timestamp" and I want to change this to "timestamptz", then, based on the above, this should work:
ALTER TABLE checks ALTER COLUMN check_date_time TYPE timestamptz;
But guess what... it doesn't. I keep getting the error message
Error executing SQL statement. ERROR: syntax error at end of input
Position: 19 - Connection: Test: 31ms
Everything else works, by the way. I can create tables, do selects, etc., just ALTER is giving me these troubles. I've been at this for about an hour now and I'm really running out of ideas as to what issues it could have. Any suggestions would be very welcome.
The correct timezone might not being applied on the conversion, if you don't explicit this, the timezone will be assumed, and typically isn't UTC.
ALTER TABLE checks
ALTER check_date_time TYPE timestamptz USING check_date_time AT TIME ZONE 'UTC'
, ALTER check_date_time SET DEFAULT now();