1 files changed, 0 insertions, 217 deletions
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index e753b8c3452..5ffcd4ffc7b 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -196,48 +196,6 @@ INSERT INTO ATACC2 (TEST2) VALUES (3);
 INSERT INTO ATACC1 (TEST2) VALUES (3);
 DROP TABLE ATACC1 CASCADE;
 
--- NOT NULL NO INHERIT
-CREATE TABLE ATACC1 (a int, not null a no inherit);
-CREATE TABLE ATACC2 () INHERITS (ATACC1);
-\d+ ATACC2
-DROP TABLE ATACC1, ATACC2;
-CREATE TABLE ATACC1 (a int);
-ALTER TABLE ATACC1 ADD NOT NULL a NO INHERIT;
-CREATE TABLE ATACC2 () INHERITS (ATACC1);
-\d+ ATACC2
-DROP TABLE ATACC1, ATACC2;
-CREATE TABLE ATACC1 (a int);
-CREATE TABLE ATACC2 () INHERITS (ATACC1);
-ALTER TABLE ATACC1 ADD NOT NULL a NO INHERIT;
-\d+ ATACC2
-DROP TABLE ATACC1, ATACC2;
-
--- no can do
-CREATE TABLE ATACC1 (a int NOT NULL NO INHERIT) PARTITION BY LIST (a);
-CREATE TABLE ATACC1 (a int, NOT NULL a NO INHERIT) PARTITION BY LIST (a);
-
--- overridding a no-inherit constraint with an inheritable one
-CREATE TABLE ATACC2 (a int, CONSTRAINT a_is_not_null NOT NULL a NO INHERIT);
-CREATE TABLE ATACC1 (a int);
-CREATE TABLE ATACC3 (a int) INHERITS (ATACC2);
-INSERT INTO ATACC3 VALUES (null);	-- make sure we scan atacc3
-ALTER TABLE ATACC2 INHERIT ATACC1;
-ALTER TABLE ATACC1 ADD CONSTRAINT ditto NOT NULL a;
-DELETE FROM ATACC3;
-ALTER TABLE ATACC1 ADD CONSTRAINT ditto NOT NULL a;
-\d+ ATACC[123]
-ALTER TABLE ATACC2 DROP CONSTRAINT a_is_not_null;
-ALTER TABLE ATACC1 DROP CONSTRAINT ditto;
-\d+ ATACC3
-DROP TABLE ATACC1, ATACC2, ATACC3;
-
--- The same cannot be achieved this way
-CREATE TABLE ATACC2 (a int, CONSTRAINT a_is_not_null NOT NULL a NO INHERIT);
-CREATE TABLE ATACC1 (a int, CONSTRAINT ditto NOT NULL a);
-CREATE TABLE ATACC3 (a int) INHERITS (ATACC2);
-ALTER TABLE ATACC2 INHERIT ATACC1;
-DROP TABLE ATACC1, ATACC2, ATACC3;
-
 --
 -- Check constraints on INSERT INTO
 --
@@ -598,181 +556,6 @@ ALTER TABLE deferred_excl ADD EXCLUDE (f1 WITH =);
 
 DROP TABLE deferred_excl;
 
--- verify constraints created for NOT NULL clauses
-CREATE TABLE notnull_tbl1 (a INTEGER NOT NULL NOT NULL);
-\d+ notnull_tbl1
-select conname, contype, conkey from pg_constraint where conrelid = 'notnull_tbl1'::regclass;
--- no-op
-ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a;
-\d+ notnull_tbl1
--- duplicate name
-ALTER TABLE notnull_tbl1 ADD COLUMN b INT CONSTRAINT notnull_tbl1_a_not_null NOT NULL;
--- DROP NOT NULL gets rid of both the attnotnull flag and the constraint itself
-ALTER TABLE notnull_tbl1 ALTER a DROP NOT NULL;
-\d notnull_tbl1
-select conname, contype, conkey from pg_constraint where conrelid = 'notnull_tbl1'::regclass;
--- SET NOT NULL puts both back
-ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL;
-\d notnull_tbl1
-select conname, contype, conkey from pg_constraint where conrelid = 'notnull_tbl1'::regclass;
--- Doing it twice doesn't create a redundant constraint
-ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL;
-select conname, contype, conkey from pg_constraint where conrelid = 'notnull_tbl1'::regclass;
--- Using the "table constraint" syntax also works
-ALTER TABLE notnull_tbl1 ALTER a DROP NOT NULL;
-ALTER TABLE notnull_tbl1 ADD CONSTRAINT foobar NOT NULL a;
-\d notnull_tbl1
-select conname, contype, conkey from pg_constraint where conrelid = 'notnull_tbl1'::regclass;
-DROP TABLE notnull_tbl1;
-
--- nope
-CREATE TABLE notnull_tbl2 (a INTEGER CONSTRAINT blah NOT NULL, b INTEGER CONSTRAINT blah NOT NULL);
-
--- can't drop not-null in primary key
-CREATE TABLE notnull_tbl2 (a INTEGER PRIMARY KEY);
-ALTER TABLE notnull_tbl2 ALTER a DROP NOT NULL;
-DROP TABLE notnull_tbl2;
-
--- make sure attnotnull is reset correctly when a PK is dropped indirectly,
--- or kept if there's a reason for that
-CREATE TABLE notnull_tbl1 (c0 int, c1 int, PRIMARY KEY (c0, c1));
-ALTER TABLE  notnull_tbl1 DROP c1;
-\d+ notnull_tbl1
-DROP TABLE notnull_tbl1;
--- same, via dropping a domain
-CREATE DOMAIN notnull_dom1 AS INTEGER;
-CREATE TABLE notnull_tbl1 (c0 notnull_dom1, c1 int, PRIMARY KEY (c0, c1));
-DROP DOMAIN notnull_dom1 CASCADE;
-\d+ notnull_tbl1
-DROP TABLE notnull_tbl1;
--- with a REPLICA IDENTITY column.  Here the not-nulls must be kept
-CREATE DOMAIN notnull_dom1 AS INTEGER;
-CREATE TABLE notnull_tbl1 (c0 notnull_dom1, c1 int UNIQUE, c2 int generated by default as identity, PRIMARY KEY (c0, c1, c2));
-ALTER TABLE notnull_tbl1 DROP CONSTRAINT notnull_tbl1_c2_not_null;
-ALTER TABLE notnull_tbl1 REPLICA IDENTITY USING INDEX notnull_tbl1_c1_key;
-DROP DOMAIN notnull_dom1 CASCADE;
-ALTER TABLE  notnull_tbl1 ALTER c1 DROP NOT NULL;	-- can't be dropped
-ALTER TABLE  notnull_tbl1 ALTER c1 SET NOT NULL;	-- can be set right
-\d+ notnull_tbl1
-DROP TABLE notnull_tbl1;
-
-CREATE DOMAIN notnull_dom2 AS INTEGER;
-CREATE TABLE notnull_tbl2 (c0 notnull_dom2, c1 int UNIQUE, c2 int generated by default as identity, PRIMARY KEY (c0, c1, c2));
-ALTER TABLE notnull_tbl2 DROP CONSTRAINT notnull_tbl2_c2_not_null;
-ALTER TABLE notnull_tbl2 REPLICA IDENTITY USING INDEX notnull_tbl2_c1_key;
-DROP DOMAIN notnull_dom2 CASCADE;
-\d+ notnull_tbl2
-BEGIN;
-/* make sure the table can be put right, but roll that back */
-ALTER TABLE notnull_tbl2 REPLICA IDENTITY FULL, ALTER c2 DROP IDENTITY;
-ALTER TABLE notnull_tbl2 ALTER c1 DROP NOT NULL, ALTER c2 DROP NOT NULL;
-\d+ notnull_tbl2
-ROLLBACK;
--- Leave this table around for pg_upgrade testing
-
-CREATE TABLE notnull_tbl3 (a INTEGER NOT NULL, CHECK (a IS NOT NULL));
-ALTER TABLE notnull_tbl3 ALTER A DROP NOT NULL;
-ALTER TABLE notnull_tbl3 ADD b int, ADD CONSTRAINT pk PRIMARY KEY (a, b);
-\d notnull_tbl3
-ALTER TABLE notnull_tbl3 DROP CONSTRAINT pk;
-\d notnull_tbl3
-
--- Primary keys in parent table cause NOT NULL constraint to spawn on their
--- children.  Verify that they work correctly.
-CREATE TABLE cnn_parent (a int, b int);
-CREATE TABLE cnn_child () INHERITS (cnn_parent);
-CREATE TABLE cnn_grandchild (NOT NULL b) INHERITS (cnn_child);
-CREATE TABLE cnn_child2 (NOT NULL a NO INHERIT) INHERITS (cnn_parent);
-CREATE TABLE cnn_grandchild2 () INHERITS (cnn_grandchild, cnn_child2);
-
-ALTER TABLE cnn_parent ADD PRIMARY KEY (b);
-\d+ cnn_grandchild
-\d+ cnn_grandchild2
-ALTER TABLE cnn_parent DROP CONSTRAINT cnn_parent_pkey;
-\set VERBOSITY terse
-DROP TABLE cnn_parent CASCADE;
-\set VERBOSITY default
-
--- As above, but create the primary key ahead of time
-CREATE TABLE cnn_parent (a int, b int PRIMARY KEY);
-CREATE TABLE cnn_child () INHERITS (cnn_parent);
-CREATE TABLE cnn_grandchild (NOT NULL b) INHERITS (cnn_child);
-CREATE TABLE cnn_child2 (NOT NULL a NO INHERIT) INHERITS (cnn_parent);
-CREATE TABLE cnn_grandchild2 () INHERITS (cnn_grandchild, cnn_child2);
-
-ALTER TABLE cnn_parent ADD PRIMARY KEY (b);
-\d+ cnn_grandchild
-\d+ cnn_grandchild2
-ALTER TABLE cnn_parent DROP CONSTRAINT cnn_parent_pkey;
-\set VERBOSITY terse
-DROP TABLE cnn_parent CASCADE;
-\set VERBOSITY default
-
--- As above, but create the primary key using a UNIQUE index
-CREATE TABLE cnn_parent (a int, b int);
-CREATE TABLE cnn_child () INHERITS (cnn_parent);
-CREATE TABLE cnn_grandchild (NOT NULL b) INHERITS (cnn_child);
-CREATE TABLE cnn_child2 (NOT NULL a NO INHERIT) INHERITS (cnn_parent);
-CREATE TABLE cnn_grandchild2 () INHERITS (cnn_grandchild, cnn_child2);
-
-CREATE UNIQUE INDEX b_uq ON cnn_parent (b);
-ALTER TABLE cnn_parent ADD PRIMARY KEY USING INDEX b_uq;
-\d+ cnn_grandchild
-\d+ cnn_grandchild2
-ALTER TABLE cnn_parent DROP CONSTRAINT cnn_parent_pkey;
--- keeps these tables around, for pg_upgrade testing
-
--- A primary key shouldn't attach to a unique constraint
-create table cnn2_parted (a int primary key) partition by list (a);
-create table cnn2_part1 (a int unique);
-alter table cnn2_parted attach partition cnn2_part1 for values in (1);
-\d+ cnn2_part1
-drop table cnn2_parted;
-
--- ensure columns in partitions are marked not-null
-create table cnn2_parted(a int primary key) partition by list (a);
-create table cnn2_part1(a int);
-alter table cnn2_parted attach partition cnn2_part1 for values in (1);
-insert into cnn2_part1 values (null);
-drop table cnn2_parted, cnn2_part1;
-
-create table cnn2_parted(a int not null) partition by list (a);
-create table cnn2_part1(a int primary key);
-alter table cnn2_parted attach partition cnn2_part1 for values in (1);
-drop table cnn2_parted, cnn2_part1;
-
-create table cnn2_parted(a int) partition by list (a);
-create table cnn_part1 partition of cnn2_parted for values in (1, null);
-insert into cnn_part1 values (null);
-alter table cnn2_parted add primary key (a);
-drop table cnn2_parted;
-
--- columns in regular and LIKE inheritance should be marked not-nullable
--- for primary keys, even if those are deferred
-CREATE TABLE notnull_tbl4 (a INTEGER PRIMARY KEY INITIALLY DEFERRED);
-CREATE TABLE notnull_tbl4_lk (LIKE notnull_tbl4);
-CREATE TABLE notnull_tbl4_lk2 (LIKE notnull_tbl4 INCLUDING INDEXES);
-CREATE TABLE notnull_tbl4_lk3 (LIKE notnull_tbl4 INCLUDING INDEXES, CONSTRAINT a_nn NOT NULL a);
-CREATE TABLE notnull_tbl4_cld () INHERITS (notnull_tbl4);
-CREATE TABLE notnull_tbl4_cld2 (PRIMARY KEY (a) DEFERRABLE) INHERITS (notnull_tbl4);
-CREATE TABLE notnull_tbl4_cld3 (PRIMARY KEY (a) DEFERRABLE, CONSTRAINT a_nn NOT NULL a) INHERITS (notnull_tbl4);
-\d+ notnull_tbl4
-\d+ notnull_tbl4_lk
-\d+ notnull_tbl4_lk2
-\d+ notnull_tbl4_lk3
-\d+ notnull_tbl4_cld
-\d+ notnull_tbl4_cld2
-\d+ notnull_tbl4_cld3
--- leave these tables around for pg_upgrade testing
-
--- also, if a NOT NULL is dropped underneath a deferrable PK, the column
--- should still be nullable afterwards.  This mimics what pg_dump does.
-CREATE TABLE notnull_tbl5 (a INTEGER CONSTRAINT a_nn NOT NULL);
-ALTER TABLE notnull_tbl5 ADD PRIMARY KEY (a) DEFERRABLE;
-ALTER TABLE notnull_tbl5 DROP CONSTRAINT a_nn;
-\d+ notnull_tbl5
-DROP TABLE notnull_tbl5;
-
 -- Comments
 -- Setup a low-level role to enforce non-superuser checks.
 CREATE ROLE regress_constraint_comments;