Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tbl1(id serial, ts_col timestamp);
INSERT INTO tbl1(ts_col) 
SELECT now() FROM generate_series(1,10);
DELETE FROM tbl1;                         -- dead dummy tuples
INSERT INTO tbl1(ts_col) 
SELECT now() + interval '1 sec' * g FROM generate_series(1,10) g;  -- dummy tuples

CREATE TABLE tbl2(id serial, ts_col timestamp);
INSERT INTO tbl2(ts_col) 
SELECT now() FROM generate_series(1,10);
DELETE FROM tbl2;                         -- dead dummy tuples
INSERT INTO tbl2(ts_col) 
SELECT now() + interval '1 sec' * g FROM generate_series(1,10) g;  -- dummy tuples

CREATE TABLE tbl3 (id serial, txt_col varchar(10));
INSERT INTO tbl3(txt_col)
SELECT 'foo' FROM generate_series(1,10);
DELETE FROM tbl3;                         -- dead dummy tuples
INSERT INTO tbl3(txt_col) 
SELECT 'foo' || g::text FROM generate_series(1,10) g;  -- dummy tuples

SHOW timezone;

CREATE TABLE

INSERT 0 10

DELETE 10

INSERT 0 10

CREATE TABLE

INSERT 0 10

DELETE 10

INSERT 0 10

CREATE TABLE

INSERT 0 10

DELETE 10

INSERT 0 10

TimeZone

GB

SHOW

-- tbl1 - convert with current time zone
SELECT ctid, xmin, ts_col::text FROM tbl1 LIMIT 2;
ALTER TABLE tbl1 ALTER ts_col TYPE timestamptz;
SELECT ctid, xmin, ts_col::text FROM tbl1 LIMIT 2;

ctid
xmin
ts_col

(0,11)
1115079
2024-10-07 10:18:59.583723

(0,12)
1115079
2024-10-07 10:19:00.583723

SELECT 2

ALTER TABLE

ctid
xmin
ts_col

(0,1)
1115080
2024-10-07 10:18:59.583723+01

(0,2)
1115080
2024-10-07 10:19:00.583723+01

SELECT 2

-- Display tbl1 with different time zone
SET timezone = 'Europe/Vienna';
SELECT ctid, xmin, ts_col::text FROM tbl1 LIMIT 2;

SET

ctid
xmin
ts_col

(0,1)
1115080
2024-10-07 11:18:59.583723+02

(0,2)
1115080
2024-10-07 11:19:00.583723+02

SELECT 2

-- tbl2 - convert with different time zone
SELECT ctid, xmin, ts_col::text FROM tbl2 LIMIT 2;
ALTER TABLE tbl2 ALTER ts_col TYPE timestamptz;
SELECT ctid, xmin, ts_col::text FROM tbl2 LIMIT 2;

ctid
xmin
ts_col

(0,11)
1115079
2024-10-07 10:18:59.583723

(0,12)
1115079
2024-10-07 10:19:00.583723

SELECT 2

ALTER TABLE

ctid
xmin
ts_col

(0,1)
1115081
2024-10-07 10:18:59.583723+02

(0,2)
1115081
2024-10-07 10:19:00.583723+02

SELECT 2

-- Compare tbl1 and tbl2 - display with current time zone
RESET timezone;
SELECT xmin, ts_col::text FROM tbl1 LIMIT 2;
SELECT xmin, ts_col::text FROM tbl2 LIMIT 2;

RESET

xmin
ts_col

1115080
2024-10-07 10:18:59.583723+01

1115080
2024-10-07 10:19:00.583723+01

SELECT 2

xmin
ts_col

1115081
2024-10-07 09:18:59.583723+01

1115081
2024-10-07 09:19:00.583723+01

SELECT 2

-- Compare conversion of varchar(10) -> varchar(20) -> text
SELECT ctid, xmin, * FROM tbl3 LIMIT 2;
ALTER TABLE tbl3 ALTER txt_col TYPE varchar(20);  -- converted without rewrite
SELECT ctid, xmin, * FROM tbl3 LIMIT 2;  -- same xmin!

ctid
xmin
id
txt_col

(0,11)
1115079
11
foo1

(0,12)
1115079
12
foo2

SELECT 2

ALTER TABLE

ctid
xmin
id
txt_col

(0,11)
1115079
11
foo1

(0,12)
1115079
12
foo2

SELECT 2

ALTER TABLE tbl3 ALTER txt_col TYPE text;
SELECT ctid, xmin, * FROM tbl3 LIMIT 2;

ALTER TABLE tbl3 ALTER txt_col TYPE varchar(5);
SELECT ctid, xmin, * FROM tbl3 LIMIT 2;

ALTER TABLE

ctid
xmin
id
txt_col

(0,11)
1115079
11
foo1

(0,12)
1115079
12
foo2

SELECT 2

ALTER TABLE

ctid
xmin
id
txt_col

(0,1)
1115083
11
foo1

(0,2)
1115083
12
foo2

SELECT 2

ctid	xmin	ts_col
(0,11)	1115079	2024-10-07 10:18:59.583723
(0,12)	1115079	2024-10-07 10:19:00.583723

ctid	xmin	ts_col
(0,1)	1115080	2024-10-07 10:18:59.583723+01
(0,2)	1115080	2024-10-07 10:19:00.583723+01

ctid	xmin	ts_col
(0,1)	1115080	2024-10-07 11:18:59.583723+02
(0,2)	1115080	2024-10-07 11:19:00.583723+02

ctid	xmin	ts_col
(0,1)	1115081	2024-10-07 10:18:59.583723+02
(0,2)	1115081	2024-10-07 10:19:00.583723+02

xmin	ts_col
1115081	2024-10-07 09:18:59.583723+01
1115081	2024-10-07 09:19:00.583723+01