Question about new pg12 feature no-rewrite timestamp to timestamptz conversion

From: Jeremy Finzel <finzelj(at)gmail(dot)com>
To: PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org>
Subject: Question about new pg12 feature no-rewrite timestamp to timestamptz conversion
Date: 2019-05-20 18:13:50
Message-ID: CAMa1XUiVntazopn4YUpr=QOeAqZ=Z-EK3xvrbJbOcsNzQMqfOQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I have a question about this (really exciting) feature coming in pg12:

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

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

We actually want to migrate all of our databases to timestamptz
everywhere. But some of them have historically saved data in a *local*
time zone with data type timestamp.

I assume there is no similarly easy way to do this alter type without a
table rewrite for a local time zone? I would assume DST changes would be
an issue here.

But it would be really nice if we have a table with timestamp data saved @
America/Chicago time zone, to set the session to 'America/Chicago' and
alter type to timestamptz, and similarly avoid a table rewrite. Is this
possible or feasible?

Thank you!
Jeremy

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2019-05-20 18:17:14 Re: Multivariate MCV stats can leak data to unprivileged users
Previous Message Juan José Santamaría Flecha 2019-05-20 17:46:00 Compile using the Visual Studio 2019