15

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

Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
user2597012
  • 581
  • 4
  • 9
  • 28

4 Answers4

10

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 ]
Tomasz Myrta
  • 1,114
  • 8
  • 10
  • 12
    An [example](http://www.postgresql.org/docs/current/static/sql-altertable.html#AEN67057) from the docs covers this case perfectly: `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';` – Milen A. Radev Oct 24 '13 at 08:35
  • 1
    Just tell PostgreSQL what time zone to use in the USING expression – Frank Heikens Oct 24 '13 at 10:54
  • Can anyone actually explain how that using expression works? I want to alter disallow a boolean column from being null, but it won't work because some of the values are already null. I want to use a using expression to change that – tamj0rd2 Jun 12 '20 at 13:57
8

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;

Ritesh Jha
  • 291
  • 3
  • 6
6

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';
Frank Conry
  • 2,694
  • 3
  • 29
  • 35
1

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
Bashir Saidi Wamula
  • 117
  • 7