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?

Improve this question

1 Answer 1

Reset to default
2

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

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

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.