Alter timestamptz column to timestamp, without re-writing the table

Question

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?

Answer 1

You already saw my related answer addressing the reverse conversion timestamp → timestamptz without table rewrite:

• ALTER timestamp column to timestamptz, without "converting" data?

Well, since Postgres 12 the same "easter egg" feature applies to timestamptz → timestamp 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).