Postgres 12

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, ts_col timestamp);
INSERT INTO tbl3(ts_col) 
SELECT now() FROM generate_series(1,10);
DELETE FROM tbl3;                         -- dead dummy tuples
INSERT INTO tbl3(ts_col) 
SELECT now() + interval '1 sec' * g FROM generate_series(1,10) g;  -- dummy tuples

SET timezone = 'Europe/London';

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

SET

-- tbl1 - convert with session time zone <> 'UTC'
--> table rewrite :(
SELECT ctid, xmin, ts_col::text FROM tbl1 LIMIT 2;

ctid
xmin
ts_col

(0,11)
3024900
2026-06-22 09:40:32.308522

(0,12)
3024900
2026-06-22 09:40:33.308522

SELECT 2

SET timezone = 'Europe/London';
ALTER TABLE tbl1 ALTER ts_col TYPE timestamptz;
SELECT ctid, xmin, ts_col::text FROM tbl1 LIMIT 2;

SET

ALTER TABLE

ctid
xmin
ts_col

(0,1)
3024901
2026-06-22 09:40:32.308522+01

(0,2)
3024901
2026-06-22 09:40:33.308522+01

SELECT 2

-- tbl2 - convert with UTC in USING clause
--> table rewrite :(
SELECT ctid, xmin, ts_col::text FROM tbl2 LIMIT 2;

ctid
xmin
ts_col

(0,11)
3024900
2026-06-22 09:40:32.308522

(0,12)
3024900
2026-06-22 09:40:33.308522

SELECT 2

ALTER TABLE tbl2 ALTER ts_col TYPE timestamptz USING ts_col AT TIME ZONE 'UTC';
SELECT ctid, xmin, ts_col::text FROM tbl2 LIMIT 2;

ALTER TABLE

ctid
xmin
ts_col

(0,1)
3024902
2026-06-22 10:40:32.308522+01

(0,2)
3024902
2026-06-22 10:40:33.308522+01

SELECT 2

-- tbl4 - convert with session time zone set to UTC
--> NO table rewrite in pg 12+ :)
SELECT ctid, xmin, ts_col::text FROM tbl3 LIMIT 2;

ctid
xmin
ts_col

(0,11)
3024900
2026-06-22 09:40:32.308522

(0,12)
3024900
2026-06-22 09:40:33.308522

SELECT 2

SET timezone = 'UTC';
ALTER TABLE tbl3 ALTER ts_col TYPE timestamptz;
SELECT ctid, xmin, ts_col::text FROM tbl3 LIMIT 2;

SET

ALTER TABLE

ctid
xmin
ts_col

(0,11)
3024900
2026-06-22 09:40:32.308522+00

(0,12)
3024900
2026-06-22 09:40:33.308522+00

SELECT 2

ctid	xmin	ts_col
(0,11)	3024900	2026-06-22 09:40:32.308522
(0,12)	3024900	2026-06-22 09:40:33.308522

ctid	xmin	ts_col
(0,1)	3024901	2026-06-22 09:40:32.308522+01
(0,2)	3024901	2026-06-22 09:40:33.308522+01

ctid	xmin	ts_col
(0,1)	3024902	2026-06-22 10:40:32.308522+01
(0,2)	3024902	2026-06-22 10:40:33.308522+01

ctid	xmin	ts_col
(0,11)	3024900	2026-06-22 09:40:32.308522+00
(0,12)	3024900	2026-06-22 09:40:33.308522+00