1

I'm using Postgres Debezium source connectors, which does not support columns with timezone. I want to convert a table column from type from timestamptz to timestamp.

Postgres table stores data in UTC time zone only, ideally there should not be any value change from this conversion. What is the best way to achieve this?

1 Answer 1

1

You already saw my related answer addressing the reverse conversion timestamptimestamptz without table rewrite:

Well, since Postgres 12 the same "easter egg" feature applies to timestamptztimestamp as well. The release notes:

Allow ALTER TABLE ... SET DATA TYPE changing between timestamp and timestamptz to avoid a table rewrite when the session time zone is UTC (Noah Misch)

In the UTC time zone, these two data types are binary compatible.

So just:

SET timezone = 'UTC';  -- unless already set to UTC
ALTER TABLE tbl ALTER COLUMN ts TYPE timestamp;
RESET timezone;        -- optional

And you don't have to mess with system catalogs directly (which should be avoided if at all possible).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.