Postgres 11

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)
818372
2025-02-17 12:27:07.985314

(0,12)
818372
2025-02-17 12:27:08.985314

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)
818373
2025-02-17 12:27:07.985314+00

(0,2)
818373
2025-02-17 12:27:08.985314+00

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)
818372
2025-02-17 12:27:07.985314

(0,12)
818372
2025-02-17 12:27:08.985314

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)
818374
2025-02-17 12:27:07.985314+00

(0,2)
818374
2025-02-17 12:27:08.985314+00

SELECT 2

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

ctid
xmin
ts_col

(0,11)
818372
2025-02-17 12:27:07.985314

(0,12)
818372
2025-02-17 12:27:08.985314

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,1)
818375
2025-02-17 12:27:07.985314+00

(0,2)
818375
2025-02-17 12:27:08.985314+00

SELECT 2

ctid	xmin	ts_col
(0,11)	818372	2025-02-17 12:27:07.985314
(0,12)	818372	2025-02-17 12:27:08.985314

ctid	xmin	ts_col
(0,1)	818373	2025-02-17 12:27:07.985314+00
(0,2)	818373	2025-02-17 12:27:08.985314+00

ctid	xmin	ts_col
(0,1)	818374	2025-02-17 12:27:07.985314+00
(0,2)	818374	2025-02-17 12:27:08.985314+00

ctid	xmin	ts_col
(0,1)	818375	2025-02-17 12:27:07.985314+00
(0,2)	818375	2025-02-17 12:27:08.985314+00