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
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? |