1 files changed, 185 insertions, 0 deletions
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index e3e3bea7091..e607eb1fddb 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -6,6 +6,7 @@
 --  - PRIMARY KEY clauses
 --  - UNIQUE clauses
 --  - EXCLUDE clauses
+--  - NOT NULL clauses
 --
 
 -- directory paths are passed to us in environment variables
@@ -597,6 +598,190 @@ 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
+-- 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
+-- SET NOT NULL puts both back
+ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL;
+\d+ notnull_tbl1
+-- 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
+DROP TABLE notnull_tbl1;
+
+-- Verify that constraint names and NO INHERIT are properly considered when
+-- multiple constraint are specified, either explicitly or via SERIAL/PK/etc,
+-- and that conflicting cases are rejected.  Mind that table constraints
+-- handle this separately from column constraints.
+create table notnull_tbl1 (a int primary key constraint foo not null);
+\d+ notnull_tbl1
+create table notnull_tbl2 (a serial, constraint foo not null a);
+\d+ notnull_tbl2
+create table notnull_tbl3 (constraint foo not null a, a int generated by default as identity);
+\d+ notnull_tbl3
+create table notnull_tbl4 (a int not null constraint foo not null);
+\d+ notnull_tbl4
+create table notnull_tbl5 (a int constraint foo not null constraint foo not null);
+\d+ notnull_tbl5
+create table notnull_tbl6 (like notnull_tbl1, constraint foo not null a);
+\d+ notnull_tbl6
+drop table notnull_tbl2, notnull_tbl3, notnull_tbl4, notnull_tbl5, notnull_tbl6;
+
+-- error cases:
+create table notnull_tbl_fail (a serial constraint foo not null constraint bar not null);
+create table notnull_tbl_fail (a serial constraint foo not null no inherit constraint foo not null);
+create table notnull_tbl_fail (a int constraint foo not null, constraint foo not null a no inherit);
+create table notnull_tbl_fail (a serial constraint foo not null, constraint bar not null a);
+create table notnull_tbl_fail (a serial, constraint foo not null a, constraint bar not null a);
+create table notnull_tbl_fail (a serial, constraint foo not null a no inherit);
+create table notnull_tbl_fail (a serial not null no inherit);
+create table notnull_tbl_fail (like notnull_tbl1, constraint foo2 not null a);
+create table notnull_tbl_fail (a int primary key constraint foo not null no inherit);
+create table notnull_tbl_fail (a int not null no inherit primary key);
+create table notnull_tbl_fail (a int primary key, not null a no inherit);
+create table notnull_tbl_fail (a int, primary key(a), not null a no inherit);
+create table notnull_tbl_fail (a int generated by default as identity, constraint foo not null a no inherit);
+create table notnull_tbl_fail (a int generated by default as identity not null no inherit);
+
+drop table notnull_tbl1;
+
+-- 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
+CREATE TABLE ATACC3 (PRIMARY KEY (a)) INHERITS (ATACC1);
+\d+ ATACC3
+DROP TABLE ATACC1, ATACC2, ATACC3;
+
+-- NOT NULL NO INHERIT is not possible on partitioned tables
+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);
+
+-- it's not possible to override 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);
+ALTER TABLE ATACC2 INHERIT ATACC1;
+-- can't override
+ALTER TABLE ATACC1 ADD CONSTRAINT ditto NOT NULL a;
+-- dropping the NO INHERIT constraint allows this to work
+ALTER TABLE ATACC2 DROP CONSTRAINT a_is_not_null;
+ALTER TABLE ATACC1 ADD CONSTRAINT ditto NOT NULL a;
+\d+ ATACC3
+DROP TABLE ATACC1, ATACC2, ATACC3;
+
+-- Can't have two constraints with the same name
+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;
+
+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 cause not-null constraints to be created.
+CREATE TABLE cnn_pk (a int, b int);
+CREATE TABLE cnn_pk_child () INHERITS (cnn_pk);
+ALTER TABLE cnn_pk ADD CONSTRAINT cnn_primarykey PRIMARY KEY (b);
+\d+ cnn_pk*
+ALTER TABLE cnn_pk DROP CONSTRAINT cnn_primarykey;
+\d+ cnn_pk*
+DROP TABLE cnn_pk, cnn_pk_child;
+
+-- As above, but create the primary key ahead of time
+CREATE TABLE cnn_pk (a int, b int, CONSTRAINT cnn_primarykey PRIMARY KEY (b));
+CREATE TABLE cnn_pk_child () INHERITS (cnn_pk);
+\d+ cnn_pk*
+ALTER TABLE cnn_pk DROP CONSTRAINT cnn_primarykey;
+\d+ cnn_pk*
+DROP TABLE cnn_pk, cnn_pk_child;
+
+-- As above, but create the primary key using a UNIQUE index
+CREATE TABLE cnn_pk (a int, b int);
+CREATE UNIQUE INDEX cnn_uq ON cnn_pk (b);
+CREATE TABLE cnn_pk_child () INHERITS (cnn_pk);
+ALTER TABLE cnn_pk ADD CONSTRAINT cnn_primarykey PRIMARY KEY USING INDEX cnn_uq;
+\d+ cnn_pk*
+DROP TABLE cnn_pk, cnn_pk_child;
+
+-- Unique constraints don't give raise to not-null constraints, however.
+create table cnn_uq (a int);
+alter table cnn_uq add unique (a);
+\d+ cnn_uq
+drop table cnn_uq;
+create table cnn_uq (a int);
+create unique index cnn_uq_idx on cnn_uq (a);
+alter table cnn_uq add unique using index cnn_uq_idx;
+\d+ cnn_uq
+
+-- Ensure partitions are scanned for null values when adding a PK
+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, NOT NULL a);
+ALTER TABLE notnull_tbl4_lk3 RENAME CONSTRAINT notnull_tbl4_a_not_null TO a_nn;
+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
+
+-- It's possible to remove a constraint from parents without affecting children
+CREATE TABLE notnull_tbl5 (a int CONSTRAINT ann NOT NULL,
+	b int CONSTRAINT bnn NOT NULL);
+CREATE TABLE notnull_tbl5_child () INHERITS (notnull_tbl5);
+ALTER TABLE ONLY notnull_tbl5 DROP CONSTRAINT ann;
+ALTER TABLE ONLY notnull_tbl5 ALTER b DROP NOT NULL;
+\d+ notnull_tbl5_child
+CREATE TABLE notnull_tbl6 (a int CONSTRAINT ann NOT NULL,
+	b int CONSTRAINT bnn NOT NULL, check (a > 0)) PARTITION BY LIST (a);
+CREATE TABLE notnull_tbl6_1 PARTITION OF notnull_tbl6 FOR VALUES IN (1);
+ALTER TABLE ONLY notnull_tbl6 DROP CONSTRAINT ann;
+ALTER TABLE ONLY notnull_tbl6 ALTER b DROP NOT NULL;
+\d+ notnull_tbl6_1
+
 -- Comments
 -- Setup a low-level role to enforce non-superuser checks.
 CREATE ROLE regress_constraint_comments;