PostgreSQL: Alter column type without data conversion for compatible types-CodePudding

I have a large table shipments with the column time which has the data type timestamp(0) with time zone, i.e. all microseconds are rounded by PostgreSQL to seconds.

I'd like to convert time's data type to allow microseconds, i.e. timestamp with time zone, but the table is large, so I'd like to alter the type:

ALTER TABLE shipments
  ALTER COLUMN time
  TYPE timestamptz;

...but do not convert data stored in this column, taking into account timestamp(0) and timestamp are perfectly compatible - because in my case data conversion will take a lot of time. How can I accomplish this?

Thanks in advance!

CodePudding user response：

ALTER TABLE shipments ALTER COLUMN time TYPE timestamptz

won't convert the data which are already stored in your table.

Only the new inserted data and updated data will be stored with the microseconds.

See the demo in dbfiddle.

CodePudding user response：

Widening the value is not a problem. But changing from 'without timezone' to 'with timezone' probably is. It will not rewrite the table if your setting of "timezone" at the time you run the command is 'UTC'.

If the setting is not UTC, then it will need to rewrite the table so it can apply the offset. (I don't know that I agree this is what it should do, I haven't thought it through before. But nonetheless that is what it does do.)