Re: Alter timestamp without timezone to with timezone rewrites rows

On Wed, Jan 13, 2021 at 4:24 PM Dorian Hoxha <dorian(dot)hoxha(at)gmail(dot)com> wrote:
>
> Hi team,
>
> I have a table with a column of "timestamp without timezone" and I want to alter it to a column of "timestamp with timezone" without rewriting the rows.
>
> Looking at the docs, and doing some quick tests, the data-on-disk is identical for both columns. But when doing an alter table, looks like it's rewriting the rows: (PostgreSQL 13.1)
>
> guru=# create table tt(m timestamp without time zone default now());
> guru=# insert into tt(m) values(now());
> guru=# SELECT xmin, xmax, cmin, cmax, m from tt;
> xmin | xmax | cmin | cmax | m
> ------+------+------+------+----------------------------
> 695 | 0 | 0 | 0 | 2021-01-13 11:47:59.146952
> (1 row)
>
> guru=# alter table tt alter column m type timestamp with time zone;
> guru=# SELECT xmin, xmax, cmin, cmax, m from tt;
> xmin | xmax | cmin | cmax | m
> ------+------+------+------+-------------------------------
> 696 | 0 | 4 | 4 | 2021-01-13 11:47:59.146952+01
> (1 row)

+01 indicates that there's timezone information added to the data, so
the rows aren't identical. Here's some more SQL run on my laptop which
shows that
postgres=# create table tt(m timestamp without time zone default now());
postgres=# insert into tt(m) values(now());
INSERT 0 1
postgres=# SELECT xmin, xmax, cmin, cmax, m from tt;
xmin | xmax | cmin | cmax | m
------+------+------+------+----------------------------
509 | 0 | 0 | 0 | 2021-01-13 19:23:25.647806
(1 row)

postgres=# show timezone;
TimeZone
--------------
Asia/Kolkata
(1 row)

postgres=# set timezone TO 'Asia/Manila';
SET
postgres=# show timezone;
TimeZone
-------------
Asia/Manila
(1 row)

postgres=# SELECT xmin, xmax, cmin, cmax, m from tt;
xmin | xmax | cmin | cmax | m
------+------+------+------+----------------------------
509 | 0 | 0 | 0 | 2021-01-13 19:23:25.647806
(1 row)

-- Note that this output is same as the above one even though I have
changed the timezone setting.

postgres=# reset timezone;
RESET
postgres=# show timezone;
TimeZone
--------------
Asia/Kolkata
(1 row)

postgres=# alter table tt alter column m type timestamp with time zone;
ALTER TABLE
postgres=# SELECT xmin, xmax, cmin, cmax, m from tt;
xmin | xmax | cmin | cmax | m
------+------+------+------+----------------------------------
510 | 0 | 4 | 4 | 2021-01-13 19:23:25.647806+05:30
(1 row)

postgres=# set timezone TO 'Asia/Manila';
SET
postgres=# SELECT xmin, xmax, cmin, cmax, m from tt;
xmin | xmax | cmin | cmax | m
------+------+------+------+-------------------------------
510 | 0 | 4 | 4 | 2021-01-13 21:53:25.647806+08
(1 row)

See the difference in the output when the timezone setting is changed.

--
Best Wishes,
Ashutosh Bapat