test.out

text/plain

Download

DROP TABLE IF EXISTS test;
DROP TABLE
CREATE TABLE test (id1 int);
CREATE TABLE
INSERT INTO test (SELECT generate_series(1,100));
INSERT 0 100
DELETE FROM test WHERE id1 < 10;
DELETE 9
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
  oid  | relfilenode 
-------+-------------
 17038 |       17038
(1 row)

will not rewrite
ALTER TABLE test ADD COLUMN id2 int;
ALTER TABLE
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
  oid  | relfilenode 
-------+-------------
 17038 |       17038
(1 row)

will not rewrite
ALTER TABLE test ADD COLUMN id3 int default 100;
ALTER TABLE
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
  oid  | relfilenode 
-------+-------------
 17038 |       17038
(1 row)

will not rewrite: stable proc
ALTER TABLE test ADD COLUMN id4 timestamp DEFAULT now();
ALTER TABLE
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
  oid  | relfilenode 
-------+-------------
 17038 |       17038
(1 row)

will rewrite: volatile proc
ALTER TABLE test ADD COLUMN id5 timestamp DEFAULT clock_timestamp();
ALTER TABLE
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
  oid  | relfilenode 
-------+-------------
 17038 |       17044
(1 row)

will rewrite: identity
ALTER TABLE test ADD COLUMN id6 int GENERATED ALWAYS AS (id1 * 2) STORED;
ALTER TABLE
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
  oid  | relfilenode 
-------+-------------
 17038 |       17048
(1 row)

will rewrite: identity
ALTER TABLE test ADD COLUMN id7 int GENERATED BY DEFAULT AS IDENTITY;
ALTER TABLE
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
  oid  | relfilenode 
-------+-------------
 17038 |       17053
(1 row)

will rewrite: domain with constraint
DROP DOMAIN IF EXISTS test_domain;
DROP DOMAIN
CREATE DOMAIN test_domain int CONSTRAINT check_test CHECK (VALUE IN (1, 2, 3));
CREATE DOMAIN
ALTER TABLE test ADD COLUMN id8 test_domain;
ALTER TABLE
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
  oid  | relfilenode 
-------+-------------
 17038 |       17059
(1 row)

will not rewrite: only constraint
ALTER TABLE test ADD COLUMN id9 int CONSTRAINT check_test CHECK (id9 IN (1, 2, 3));
ALTER TABLE
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
  oid  | relfilenode 
-------+-------------
 17038 |       17059
(1 row)

SELECT * FROM test LIMIT 10;
 id1 | id2 | id3 |            id4             |            id5             | id6 | id7 | id8 | id9 
-----+-----+-----+----------------------------+----------------------------+-----+-----+-----+-----
  10 |     | 100 | 2024-12-03 15:08:24.251233 | 2024-12-03 15:08:24.261365 |  20 |   1 |     |    
  11 |     | 100 | 2024-12-03 15:08:24.251233 | 2024-12-03 15:08:24.261427 |  22 |   2 |     |    
  12 |     | 100 | 2024-12-03 15:08:24.251233 | 2024-12-03 15:08:24.261432 |  24 |   3 |     |    
  13 |     | 100 | 2024-12-03 15:08:24.251233 | 2024-12-03 15:08:24.261434 |  26 |   4 |     |    
  14 |     | 100 | 2024-12-03 15:08:24.251233 | 2024-12-03 15:08:24.261437 |  28 |   5 |     |    
  15 |     | 100 | 2024-12-03 15:08:24.251233 | 2024-12-03 15:08:24.26144  |  30 |   6 |     |    
  16 |     | 100 | 2024-12-03 15:08:24.251233 | 2024-12-03 15:08:24.261443 |  32 |   7 |     |    
  17 |     | 100 | 2024-12-03 15:08:24.251233 | 2024-12-03 15:08:24.261445 |  34 |   8 |     |    
  18 |     | 100 | 2024-12-03 15:08:24.251233 | 2024-12-03 15:08:24.261448 |  36 |   9 |     |    
  19 |     | 100 | 2024-12-03 15:08:24.251233 | 2024-12-03 15:08:24.26145  |  38 |  10 |     |    
(10 rows)