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)
Is there any reason to rewrite the rows or is it just an optimization that no one hasn't sent a patch ?
Regards,
Dorian
