On Wed, Jan 13, 2021 at 4:24 PM Dorian Hoxha <dorian.ho...@gmail.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
