PostgreSQL: Alter column type without data conversion for compatible types

Question

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!

Edouard · Accepted Answer · 2021-12-20 17:17:04Z

1

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.

answered Dec 20, 2021 at 17:17

Edouard

7,1291 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrew Polukhin Over a year ago

Thanks for your answer, the fiddle is just awesome!

jjanes · Accepted Answer · 2021-12-20 18:47:18Z

1

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.)

answered Dec 20, 2021 at 18:47

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

1 Comment

Andrew Polukhin Over a year ago

Thanks for your answer, a great theoretical look into the problem!

Collectives™ on Stack Overflow

PostgreSQL: Alter column type without data conversion for compatible types

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related