Alter timestamp without timezone to with timezone rewrites rows

From: Dorian Hoxha <dorian(dot)hoxha(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Alter timestamp without timezone to with timezone rewrites rows
Date: 2021-01-13 10:54:07
Message-ID: CANsFX06xN-vPYxM+YXyfLezK9twjtK3dFJcOHoubTXng40muoQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Dmitry Dolgov 2021-01-13 11:21:34 Re: [PATCH] Identify LWLocks in tracepoints
Previous Message Daniel Westermann (DWE) 2021-01-13 10:39:37 src/tutorial/funcs.source: Wrong comment?