Alter a Column Data Type Postgres

Question

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

Answer 1

Postgres doesn't know how to translate int to timestamp. There are several cases and usually they have different starting date.

1. Create temporary column with timestamp
2. Update table and copy data from old column to temporary column using your own translation
3. Drop old column
4. Rename temporary column.

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 ]