test.sql

text/plain

Download

-- psql -f test.sql -e --no-psqlrc

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

\echo 'will not rewrite'
ALTER TABLE test ADD COLUMN id2 int;
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';

\echo 'will not rewrite'
ALTER TABLE test ADD COLUMN id3 int default 100;
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';

\echo 'will not rewrite: stable proc'
ALTER TABLE test ADD COLUMN id4 timestamp DEFAULT now();
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';

\echo 'will rewrite: volatile proc'
ALTER TABLE test ADD COLUMN id5 timestamp DEFAULT clock_timestamp();
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';

\echo 'will rewrite: identity'
ALTER TABLE test ADD COLUMN id6 int GENERATED ALWAYS AS (id1 * 2) STORED;
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';

\echo 'will rewrite: identity'
ALTER TABLE test ADD COLUMN id7 int GENERATED BY DEFAULT AS IDENTITY;
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';

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

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

SELECT * FROM test LIMIT 10;