Hey I have just started working on PostgreSQL, and I am wondering how can we change a column's data type, I tried the following command:
alter table tableName alter column columnName type timestamp with time zone;
However I got the following message:
column "columnName" cannot be cast to type timestamp with time zone
The current column's data type is int, and i would like to change it to timestamp
Postgres doesn't know how to translate int to timestamp. There are several cases and usually they have different starting date.
If you look into documentation, you will find one line syntax with example how to convert unix time integer type:
ALTER [ COLUMN ] column [ SET DATA ] TYPE type [ USING expression ]
ALTER TABLE foo ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone USING timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
Postgres does't allow int type column to change directly into timezone. To achive this, you have to first change column type to varchar and then change it to timezone.
alter table tableName alter column columnName type varchar(64);
alter table tableName alter column columnName type timestamp with time zone;
There is a better way to do this, with the USING clause. Like so:
ALTER TABLE tableName
ALTER columnName type TIMESTAMP WITH TIME ZONE
USING to_timestamp(columnName) AT TIME ZONE 'America/New_York';
I achieved it from timestamp to timestamp with time zone by:
ALTER TABLE tableName ALTER COLUMN columnName SET DATA TYPE timestamp with time zone;
but if it is from timestamp to int or bigint you may need to do this:
ALTER TABLE tableName ALTER COLUMN columnName SET DATA TYPE int8 USING columnName::bigint
