From ab37ba8f8a517c8aea8c6cb05eb3712b5da15955 Mon Sep 17 00:00:00 2001 From: Ashutosh Bapat Date: Wed, 3 Jan 2024 11:54:22 +0530 Subject: [PATCH 12/27] Test changing some properties of identity column in partitioned table NULL constraint, default and identity property Ashutosh Bapat --- src/test/regress/expected/identity.out | 15 +++++++++++++++ src/test/regress/sql/identity.sql | 10 ++++++++++ 2 files changed, 25 insertions(+) diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out index 0b6a4b160e..f03d88035f 100644 --- a/src/test/regress/expected/identity.out +++ b/src/test/regress/expected/identity.out @@ -701,6 +701,21 @@ SELECT tableoid::regclass, f1, f2, f3 FROM pitest3; pitest3_p1 | 07-07-2016 | from pitest3_p1 | 6 (6 rows) +-- Changing NOT NULL constraint of identity columns is not allowed +ALTER TABLE pitest1_p1 ALTER COLUMN f3 DROP NOT NULL; +ERROR: column "f3" of relation "pitest1_p1" is an identity column +ALTER TABLE pitest1 ALTER COLUMN f3 DROP NOT NULL; +ERROR: column "f3" of relation "pitest1" is an identity column +-- Identity columns have their own default +ALTER TABLE pitest1_p2 ALTER COLUMN f3 SET DEFAULT 10000; +ERROR: column "f3" of relation "pitest1_p2" is an identity column +ALTER TABLE pitest1 ALTER COLUMN f3 SET DEFAULT 10000; +ERROR: column "f3" of relation "pitest1" is an identity column +-- Adding identity to an identity column is not allowed +ALTER TABLE pitest1_p2 ALTER COLUMN f3 ADD GENERATED BY DEFAULT AS IDENTITY; +ERROR: cannot add identity to a column of a partition +ALTER TABLE pitest1 ALTER COLUMN f3 ADD GENERATED BY DEFAULT AS IDENTITY; +ERROR: column "f3" of relation "pitest1" is already an identity column -- partitions with their own identity columns are not allowed, even if the -- partitioned table does not have an identity column. CREATE TABLE pitest1_pfail PARTITION OF pitest1 ( diff --git a/src/test/regress/sql/identity.sql b/src/test/regress/sql/identity.sql index e0ce2c799b..9f35214751 100644 --- a/src/test/regress/sql/identity.sql +++ b/src/test/regress/sql/identity.sql @@ -413,6 +413,16 @@ INSERT into pitest3(f1, f2, f3) VALUES ('2016-07-6', 'from pitest3', 5); INSERT into pitest3_p1 (f1, f2, f3) VALUES ('2016-07-7', 'from pitest3_p1', 6); SELECT tableoid::regclass, f1, f2, f3 FROM pitest3; +-- Changing NOT NULL constraint of identity columns is not allowed +ALTER TABLE pitest1_p1 ALTER COLUMN f3 DROP NOT NULL; +ALTER TABLE pitest1 ALTER COLUMN f3 DROP NOT NULL; +-- Identity columns have their own default +ALTER TABLE pitest1_p2 ALTER COLUMN f3 SET DEFAULT 10000; +ALTER TABLE pitest1 ALTER COLUMN f3 SET DEFAULT 10000; +-- Adding identity to an identity column is not allowed +ALTER TABLE pitest1_p2 ALTER COLUMN f3 ADD GENERATED BY DEFAULT AS IDENTITY; +ALTER TABLE pitest1 ALTER COLUMN f3 ADD GENERATED BY DEFAULT AS IDENTITY; + -- partitions with their own identity columns are not allowed, even if the -- partitioned table does not have an identity column. CREATE TABLE pitest1_pfail PARTITION OF pitest1 ( -- 2.25.1