I have two small patches that you can integrate into your patch set:
The first just changes the punctuation of "Not-null constraints" in the
psql output to match what the documentation mostly uses.
The second has some changes to ddl.sgml to reflect that not-null
constraints are now named and can be operated on like other constraints.
You might want to read that again to make sure it matches your latest
intentions, but I think it catches all the places that are required to
change.From 324f0050eee51c47e4c558867e6cc832652b39bb Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Wed, 16 Aug 2023 10:26:09 +0200
Subject: [PATCH 1/2] fixup! Have psql print the NOT NULL constraints on \d+
---
contrib/test_decoding/expected/ddl.out | 8 +-
src/bin/psql/describe.c | 2 +-
src/test/regress/expected/create_table.out | 6 +-
.../regress/expected/create_table_like.out | 10 +--
src/test/regress/expected/foreign_data.out | 84 +++++++++----------
src/test/regress/expected/generated.out | 2 +-
src/test/regress/expected/identity.out | 2 +-
src/test/regress/expected/publication.out | 6 +-
.../regress/expected/replica_identity.out | 6 +-
src/test/regress/expected/rowsecurity.out | 2 +-
10 files changed, 64 insertions(+), 64 deletions(-)
diff --git a/contrib/test_decoding/expected/ddl.out
b/contrib/test_decoding/expected/ddl.out
index 95a0722c33..bcd1f74b2b 100644
--- a/contrib/test_decoding/expected/ddl.out
+++ b/contrib/test_decoding/expected/ddl.out
@@ -492,7 +492,7 @@ WITH (user_catalog_table = true)
options | text[] | | |
| extended | |
Indexes:
"replication_metadata_pkey" PRIMARY KEY, btree (id)
-Not null constraints:
+Not-null constraints:
"replication_metadata_id_not_null" NOT NULL "id"
"replication_metadata_relation_not_null" NOT NULL "relation"
Options: user_catalog_table=true
@@ -509,7 +509,7 @@ ALTER TABLE replication_metadata RESET (user_catalog_table);
options | text[] | | |
| extended | |
Indexes:
"replication_metadata_pkey" PRIMARY KEY, btree (id)
-Not null constraints:
+Not-null constraints:
"replication_metadata_id_not_null" NOT NULL "id"
"replication_metadata_relation_not_null" NOT NULL "relation"
@@ -525,7 +525,7 @@ ALTER TABLE replication_metadata SET (user_catalog_table =
true);
options | text[] | | |
| extended | |
Indexes:
"replication_metadata_pkey" PRIMARY KEY, btree (id)
-Not null constraints:
+Not-null constraints:
"replication_metadata_id_not_null" NOT NULL "id"
"replication_metadata_relation_not_null" NOT NULL "relation"
Options: user_catalog_table=true
@@ -547,7 +547,7 @@ ALTER TABLE replication_metadata SET (user_catalog_table =
false);
rewritemeornot | integer | | |
| plain | |
Indexes:
"replication_metadata_pkey" PRIMARY KEY, btree (id)
-Not null constraints:
+Not-null constraints:
"replication_metadata_id_not_null" NOT NULL "id"
"replication_metadata_relation_not_null" NOT NULL "relation"
Options: user_catalog_table=false
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index d1dc8fa066..4d36e0cfd8 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3072,7 +3072,7 @@ describeOneTableDetails(const char *schemaname,
tuples = PQntuples(result);
if (tuples > 0)
- printTableAddFooter(&cont, _("Not null
constraints:"));
+ printTableAddFooter(&cont, _("Not-null
constraints:"));
/* Might be an empty set - that's ok */
for (i = 0; i < tuples; i++)
diff --git a/src/test/regress/expected/create_table.out
b/src/test/regress/expected/create_table.out
index 3f6516c3f8..477e8839e9 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -854,7 +854,7 @@ drop table test_part_coll_posix;
b | integer | | not null | 1 | plain | |
Partition of: parted FOR VALUES IN ('b')
Partition constraint: ((a IS NOT NULL) AND (a = 'b'::text))
-Not null constraints:
+Not-null constraints:
"part_b_b_not_null" NOT NULL "b"
-- Both partition bound and partition key in describe output
@@ -867,7 +867,7 @@ Not null constraints:
Partition of: parted FOR VALUES IN ('c')
Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text))
Partition key: RANGE (b)
-Not null constraints:
+Not-null constraints:
"part_c_b_not_null" NOT NULL "b"
Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10)
@@ -880,7 +880,7 @@ Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10)
b | integer | | not null | 0 | plain | |
Partition of: part_c FOR VALUES FROM (1) TO (10)
Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text) AND (b IS NOT NULL)
AND (b >= 1) AND (b < 10))
-Not null constraints:
+Not-null constraints:
"part_c_b_not_null" NOT NULL "b" (inherited)
-- Show partition count in the parent's describe output
diff --git a/src/test/regress/expected/create_table_like.out
b/src/test/regress/expected/create_table_like.out
index ecac822adb..953d270455 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -333,7 +333,7 @@ CREATE TABLE ctlt12_storage (LIKE ctlt1 INCLUDING STORAGE,
LIKE ctlt2 INCLUDING
a | text | | not null | | main | |
b | text | | | | extended | |
c | text | | | | external | |
-Not null constraints:
+Not-null constraints:
"ctlt12_storage_a_not_null" NOT NULL "a"
CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING COMMENTS, LIKE ctlt2
INCLUDING COMMENTS);
@@ -344,7 +344,7 @@ CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING
COMMENTS, LIKE ctlt2 INCLUDIN
a | text | | not null | | extended | | A
b | text | | | | extended | | B
c | text | | | | extended | | C
-Not null constraints:
+Not-null constraints:
"ctlt12_comments_a_not_null" NOT NULL "a"
CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS)
INHERITS (ctlt1);
@@ -359,7 +359,7 @@ NOTICE: merging constraint "ctlt1_a_check" with inherited
definition
b | text | | | | extended | | B
Check constraints:
"ctlt1_a_check" CHECK (length(a) > 2)
-Not null constraints:
+Not-null constraints:
"ctlt1_inh_a_not_null" NOT NULL "a"
Inherits: ctlt1
@@ -382,7 +382,7 @@ Check constraints:
"ctlt1_a_check" CHECK (length(a) > 2)
"ctlt3_a_check" CHECK (length(a) < 5)
"ctlt3_c_check" CHECK (length(c) < 7)
-Not null constraints:
+Not-null constraints:
"ctlt13_inh_a_not_null" NOT NULL "a" (inherited)
Inherits: ctlt1,
ctlt3
@@ -402,7 +402,7 @@ Check constraints:
"ctlt1_a_check" CHECK (length(a) > 2)
"ctlt3_a_check" CHECK (length(a) < 5)
"ctlt3_c_check" CHECK (length(c) < 7)
-Not null constraints:
+Not-null constraints:
"ctlt13_like_a_not_null" NOT NULL "a" (inherited)
Inherits: ctlt1
diff --git a/src/test/regress/expected/foreign_data.out
b/src/test/regress/expected/foreign_data.out
index 5b242081ae..f7f5bb6766 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -742,7 +742,7 @@ COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
Check constraints:
"ft1_c2_check" CHECK (c2 <> ''::text)
"ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <=
'01-31-1994'::date)
-Not null constraints:
+Not-null constraints:
"ft1_c1_not_null" NOT NULL "c1"
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -866,7 +866,7 @@ ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STORAGE PLAIN;
Check constraints:
"ft1_c2_check" CHECK (c2 <> ''::text)
"ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <=
'01-31-1994'::date)
-Not null constraints:
+Not-null constraints:
"ft1_c1_not_null" NOT NULL "c1"
"ft1_c6_not_null" NOT NULL "c6"
Server: s0
@@ -1409,7 +1409,7 @@ CREATE FOREIGN TABLE ft2 () INHERITS (fd_pt1)
c1 | integer | | not null | | plain | |
c2 | text | | | | extended | |
c3 | date | | | | plain | |
-Not null constraints:
+Not-null constraints:
"fd_pt1_c1_not_null" NOT NULL "c1"
Child tables: ft2, FOREIGN
@@ -1420,7 +1420,7 @@ Child tables: ft2, FOREIGN
c1 | integer | | not null | | | plain |
|
c2 | text | | | | | extended |
|
c3 | date | | | | | plain |
|
-Not null constraints:
+Not-null constraints:
"fd_pt1_c1_not_null" NOT NULL "c1" (inherited)
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -1434,7 +1434,7 @@ DROP FOREIGN TABLE ft2;
c1 | integer | | not null | | plain | |
c2 | text | | | | extended | |
c3 | date | | | | plain | |
-Not null constraints:
+Not-null constraints:
"fd_pt1_c1_not_null" NOT NULL "c1"
CREATE FOREIGN TABLE ft2 (
@@ -1449,7 +1449,7 @@ CREATE FOREIGN TABLE ft2 (
c1 | integer | | not null | | | plain |
|
c2 | text | | | | | extended |
|
c3 | date | | | | | plain |
|
-Not null constraints:
+Not-null constraints:
"ft2_c1_not_null" NOT NULL "c1"
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -1462,7 +1462,7 @@ ALTER FOREIGN TABLE ft2 INHERIT fd_pt1;
c1 | integer | | not null | | plain | |
c2 | text | | | | extended | |
c3 | date | | | | plain | |
-Not null constraints:
+Not-null constraints:
"fd_pt1_c1_not_null" NOT NULL "c1"
Child tables: ft2, FOREIGN
@@ -1473,7 +1473,7 @@ Child tables: ft2, FOREIGN
c1 | integer | | not null | | | plain |
|
c2 | text | | | | | extended |
|
c3 | date | | | | | plain |
|
-Not null constraints:
+Not-null constraints:
"ft2_c1_not_null" NOT NULL "c1"
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -1496,7 +1496,7 @@ NOTICE: merging column "c3" with inherited definition
c1 | integer | | not null | | | plain |
|
c2 | text | | | | | extended |
|
c3 | date | | | | | plain |
|
-Not null constraints:
+Not-null constraints:
"ft2_c1_not_null" NOT NULL "c1"
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -1511,7 +1511,7 @@ Child tables: ct3,
c1 | integer | | not null | | plain | |
c2 | text | | | | extended | |
c3 | date | | | | plain | |
-Not null constraints:
+Not-null constraints:
"ft2_c1_not_null" NOT NULL "c1" (inherited)
Inherits: ft2
@@ -1522,7 +1522,7 @@ Inherits: ft2
c1 | integer | | not null | | | plain |
|
c2 | text | | | | | extended |
|
c3 | date | | | | | plain |
|
-Not null constraints:
+Not-null constraints:
"ft3_c1_not_null" NOT NULL "c1"
Server: s0
Inherits: ft2
@@ -1545,7 +1545,7 @@ ALTER TABLE fd_pt1 ADD COLUMN c8 integer;
c6 | integer | | | | plain | |
c7 | integer | | not null | | plain | |
c8 | integer | | | | plain | |
-Not null constraints:
+Not-null constraints:
"fd_pt1_c1_not_null" NOT NULL "c1"
"fd_pt1_c7_not_null" NOT NULL "c7"
Child tables: ft2, FOREIGN
@@ -1562,7 +1562,7 @@ Child tables: ft2, FOREIGN
c6 | integer | | | | | plain |
|
c7 | integer | | not null | | | plain |
|
c8 | integer | | | | | plain |
|
-Not null constraints:
+Not-null constraints:
"fd_pt1_c7_not_null" NOT NULL "c7" (inherited)
"ft2_c1_not_null" NOT NULL "c1"
Server: s0
@@ -1583,7 +1583,7 @@ Child tables: ct3,
c6 | integer | | | | plain | |
c7 | integer | | not null | | plain | |
c8 | integer | | | | plain | |
-Not null constraints:
+Not-null constraints:
"fd_pt1_c7_not_null" NOT NULL "c7" (inherited)
"ft2_c1_not_null" NOT NULL "c1" (inherited)
Inherits: ft2
@@ -1600,7 +1600,7 @@ Inherits: ft2
c6 | integer | | | | | plain |
|
c7 | integer | | not null | | | plain |
|
c8 | integer | | | | | plain |
|
-Not null constraints:
+Not-null constraints:
"fd_pt1_c7_not_null" NOT NULL "c7" (inherited)
"ft3_c1_not_null" NOT NULL "c1"
Server: s0
@@ -1631,7 +1631,7 @@ ALTER TABLE fd_pt1 ALTER COLUMN c8 SET STORAGE EXTERNAL;
c6 | integer | | not null | | plain | |
c7 | integer | | | | plain | |
c8 | text | | | | external | |
-Not null constraints:
+Not-null constraints:
"fd_pt1_c1_not_null" NOT NULL "c1"
"fd_pt1_c6_not_null" NOT NULL "c6"
Child tables: ft2, FOREIGN
@@ -1648,7 +1648,7 @@ Child tables: ft2, FOREIGN
c6 | integer | | not null | | | plain |
|
c7 | integer | | | | | plain |
|
c8 | text | | | | | external |
|
-Not null constraints:
+Not-null constraints:
"fd_pt1_c6_not_null" NOT NULL "c6" (inherited)
"ft2_c1_not_null" NOT NULL "c1"
Server: s0
@@ -1670,7 +1670,7 @@ ALTER TABLE fd_pt1 DROP COLUMN c8;
c1 | integer | | not null | | plain | 10000 |
c2 | text | | | | extended | |
c3 | date | | | | plain | |
-Not null constraints:
+Not-null constraints:
"fd_pt1_c1_not_null" NOT NULL "c1"
Child tables: ft2, FOREIGN
@@ -1681,7 +1681,7 @@ Child tables: ft2, FOREIGN
c1 | integer | | not null | | | plain |
10000 |
c2 | text | | | | | extended |
|
c3 | date | | | | | plain |
|
-Not null constraints:
+Not-null constraints:
"ft2_c1_not_null" NOT NULL "c1"
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -1715,7 +1715,7 @@ SELECT relname, conname, contype, conislocal,
coninhcount, connoinherit
Check constraints:
"fd_pt1chk1" CHECK (c1 > 0) NO INHERIT
"fd_pt1chk2" CHECK (c2 <> ''::text)
-Not null constraints:
+Not-null constraints:
"fd_pt1_c1_not_null" NOT NULL "c1"
Child tables: ft2, FOREIGN
@@ -1728,7 +1728,7 @@ Child tables: ft2, FOREIGN
c3 | date | | | | | plain |
|
Check constraints:
"fd_pt1chk2" CHECK (c2 <> ''::text)
-Not null constraints:
+Not-null constraints:
"ft2_c1_not_null" NOT NULL "c1"
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -1766,7 +1766,7 @@ ALTER FOREIGN TABLE ft2 INHERIT fd_pt1;
Check constraints:
"fd_pt1chk1" CHECK (c1 > 0) NO INHERIT
"fd_pt1chk2" CHECK (c2 <> ''::text)
-Not null constraints:
+Not-null constraints:
"fd_pt1_c1_not_null" NOT NULL "c1"
Child tables: ft2, FOREIGN
@@ -1779,7 +1779,7 @@ Child tables: ft2, FOREIGN
c3 | date | | | | | plain |
|
Check constraints:
"fd_pt1chk2" CHECK (c2 <> ''::text)
-Not null constraints:
+Not-null constraints:
"ft2_c1_not_null" NOT NULL "c1"
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -1800,7 +1800,7 @@ ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk3 CHECK (c2 <>
'') NOT VALID;
c3 | date | | | | plain | |
Check constraints:
"fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID
-Not null constraints:
+Not-null constraints:
"fd_pt1_c1_not_null" NOT NULL "c1"
Child tables: ft2, FOREIGN
@@ -1814,7 +1814,7 @@ Child tables: ft2, FOREIGN
Check constraints:
"fd_pt1chk2" CHECK (c2 <> ''::text)
"fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID
-Not null constraints:
+Not-null constraints:
"ft2_c1_not_null" NOT NULL "c1"
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -1831,7 +1831,7 @@ ALTER TABLE fd_pt1 VALIDATE CONSTRAINT fd_pt1chk3;
c3 | date | | | | plain | |
Check constraints:
"fd_pt1chk3" CHECK (c2 <> ''::text)
-Not null constraints:
+Not-null constraints:
"fd_pt1_c1_not_null" NOT NULL "c1"
Child tables: ft2, FOREIGN
@@ -1845,7 +1845,7 @@ Child tables: ft2, FOREIGN
Check constraints:
"fd_pt1chk2" CHECK (c2 <> ''::text)
"fd_pt1chk3" CHECK (c2 <> ''::text)
-Not null constraints:
+Not-null constraints:
"ft2_c1_not_null" NOT NULL "c1"
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -1866,7 +1866,7 @@ ALTER TABLE fd_pt1 RENAME CONSTRAINT fd_pt1chk3 TO
f2_check;
f3 | date | | | | plain | |
Check constraints:
"f2_check" CHECK (f2 <> ''::text)
-Not null constraints:
+Not-null constraints:
"fd_pt1_c1_not_null" NOT NULL "f1"
Child tables: ft2, FOREIGN
@@ -1880,7 +1880,7 @@ Child tables: ft2, FOREIGN
Check constraints:
"f2_check" CHECK (f2 <> ''::text)
"fd_pt1chk2" CHECK (f2 <> ''::text)
-Not null constraints:
+Not-null constraints:
"ft2_c1_not_null" NOT NULL "f1"
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -1928,7 +1928,7 @@ CREATE FOREIGN TABLE fd_pt2_1 PARTITION OF fd_pt2 FOR
VALUES IN (1)
c2 | text | | | | extended | |
c3 | date | | | | plain | |
Partition key: LIST (c1)
-Not null constraints:
+Not-null constraints:
"fd_pt2_c1_not_null" NOT NULL "c1"
Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN
@@ -1941,7 +1941,7 @@ Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN
c3 | date | | | | | plain |
|
Partition of: fd_pt2 FOR VALUES IN (1)
Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1))
-Not null constraints:
+Not-null constraints:
"fd_pt2_c1_not_null" NOT NULL "c1" (inherited)
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -1962,7 +1962,7 @@ CREATE FOREIGN TABLE fd_pt2_1 (
c2 | text | | | | |
extended | |
c3 | date | | | | | plain
| |
c4 | character(1) | | | | |
extended | |
-Not null constraints:
+Not-null constraints:
"fd_pt2_1_c1_not_null" NOT NULL "c1"
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -1979,7 +1979,7 @@ DROP FOREIGN TABLE fd_pt2_1;
c2 | text | | | | extended | |
c3 | date | | | | plain | |
Partition key: LIST (c1)
-Not null constraints:
+Not-null constraints:
"fd_pt2_c1_not_null" NOT NULL "c1"
Number of partitions: 0
@@ -1995,7 +1995,7 @@ CREATE FOREIGN TABLE fd_pt2_1 (
c1 | integer | | not null | | | plain |
|
c2 | text | | | | | extended |
|
c3 | date | | | | | plain |
|
-Not null constraints:
+Not-null constraints:
"fd_pt2_1_c1_not_null" NOT NULL "c1"
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -2010,7 +2010,7 @@ ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES
IN (1);
c2 | text | | | | extended | |
c3 | date | | | | plain | |
Partition key: LIST (c1)
-Not null constraints:
+Not-null constraints:
"fd_pt2_c1_not_null" NOT NULL "c1"
Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN
@@ -2023,7 +2023,7 @@ Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN
c3 | date | | | | | plain |
|
Partition of: fd_pt2 FOR VALUES IN (1)
Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1))
-Not null constraints:
+Not-null constraints:
"fd_pt2_1_c1_not_null" NOT NULL "c1" (inherited)
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -2042,7 +2042,7 @@ ALTER TABLE fd_pt2_1 ADD CONSTRAINT p21chk CHECK (c2 <>
'');
c2 | text | | | | extended | |
c3 | date | | | | plain | |
Partition key: LIST (c1)
-Not null constraints:
+Not-null constraints:
"fd_pt2_c1_not_null" NOT NULL "c1"
Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN
@@ -2057,7 +2057,7 @@ Partition of: fd_pt2 FOR VALUES IN (1)
Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1))
Check constraints:
"p21chk" CHECK (c2 <> ''::text)
-Not null constraints:
+Not-null constraints:
"fd_pt2_1_c1_not_null" NOT NULL "c1" (inherited)
"fd_pt2_1_c3_not_null" NOT NULL "c3"
Server: s0
@@ -2077,7 +2077,7 @@ ALTER TABLE fd_pt2 ALTER c2 SET NOT NULL;
c2 | text | | not null | | extended | |
c3 | date | | | | plain | |
Partition key: LIST (c1)
-Not null constraints:
+Not-null constraints:
"fd_pt2_c1_not_null" NOT NULL "c1"
"fd_pt2_c2_not_null" NOT NULL "c2"
Number of partitions: 0
@@ -2091,7 +2091,7 @@ Number of partitions: 0
c3 | date | | not null | | | plain |
|
Check constraints:
"p21chk" CHECK (c2 <> ''::text)
-Not null constraints:
+Not-null constraints:
"fd_pt2_1_c1_not_null" NOT NULL "c1"
"fd_pt2_1_c3_not_null" NOT NULL "c3"
Server: s0
@@ -2113,7 +2113,7 @@ ALTER TABLE fd_pt2 ADD CONSTRAINT fd_pt2chk1 CHECK (c1 >
0);
Partition key: LIST (c1)
Check constraints:
"fd_pt2chk1" CHECK (c1 > 0)
-Not null constraints:
+Not-null constraints:
"fd_pt2_c1_not_null" NOT NULL "c1"
"fd_pt2_c2_not_null" NOT NULL "c2"
Number of partitions: 0
@@ -2127,7 +2127,7 @@ Number of partitions: 0
c3 | date | | not null | | | plain |
|
Check constraints:
"p21chk" CHECK (c2 <> ''::text)
-Not null constraints:
+Not-null constraints:
"fd_pt2_1_c1_not_null" NOT NULL "c1"
"fd_pt2_1_c2_not_null" NOT NULL "c2"
"fd_pt2_1_c3_not_null" NOT NULL "c3"
diff --git a/src/test/regress/expected/generated.out
b/src/test/regress/expected/generated.out
index 930c5790fb..dc97ed3fe0 100644
--- a/src/test/regress/expected/generated.out
+++ b/src/test/regress/expected/generated.out
@@ -315,7 +315,7 @@ NOTICE: merging column "b" with inherited definition
a | integer | | not null |
| plain | |
b | integer | | | generated always as (a * 22) stored
| plain | |
x | integer | | |
| plain | |
-Not null constraints:
+Not-null constraints:
"gtestx_a_not_null" NOT NULL "a" (inherited)
Inherits: gtest1
diff --git a/src/test/regress/expected/identity.out
b/src/test/regress/expected/identity.out
index 733dda74b9..7c6e87e8a5 100644
--- a/src/test/regress/expected/identity.out
+++ b/src/test/regress/expected/identity.out
@@ -506,7 +506,7 @@ TABLE itest8;
f3 | integer | | not null | generated by default as identity |
plain | |
f4 | bigint | | not null | generated always as identity |
plain | |
f5 | bigint | | | |
plain | |
-Not null constraints:
+Not-null constraints:
"itest8_f2_not_null" NOT NULL "f2"
"itest8_f3_not_null" NOT NULL "f3"
"itest8_f4_not_null" NOT NULL "f4"
diff --git a/src/test/regress/expected/publication.out
b/src/test/regress/expected/publication.out
index f0ccc39630..16361a91f9 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -193,7 +193,7 @@ Indexes:
"testpub_tbl2_pkey" PRIMARY KEY, btree (id)
Publications:
"testpub_foralltables"
-Not null constraints:
+Not-null constraints:
"testpub_tbl2_id_not_null" NOT NULL "id"
\dRp+ testpub_foralltables
@@ -1149,7 +1149,7 @@ Publications:
"testpib_ins_trunct"
"testpub_default"
"testpub_fortbl"
-Not null constraints:
+Not-null constraints:
"testpub_tbl1_id_not_null" NOT NULL "id"
\dRp+ testpub_default
@@ -1176,7 +1176,7 @@ Indexes:
Publications:
"testpib_ins_trunct"
"testpub_fortbl"
-Not null constraints:
+Not-null constraints:
"testpub_tbl1_id_not_null" NOT NULL "id"
-- verify relation cache invalidation when a primary key is added using
diff --git a/src/test/regress/expected/replica_identity.out
b/src/test/regress/expected/replica_identity.out
index 4d4cb95732..6038bf8e9f 100644
--- a/src/test/regress/expected/replica_identity.out
+++ b/src/test/regress/expected/replica_identity.out
@@ -170,7 +170,7 @@ Indexes:
"test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <>
'3'::text
"test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb)
DEFERRABLE
"test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya,
keyb)
-Not null constraints:
+Not-null constraints:
"test_replica_identity_id_not_null" NOT NULL "id"
"test_replica_identity_keya_not_null" NOT NULL "keya"
"test_replica_identity_keyb_not_null" NOT NULL "keyb"
@@ -256,7 +256,7 @@ ALTER TABLE ONLY test_replica_identity4_1
Partition key: LIST (id)
Indexes:
"test_replica_identity4_pkey" PRIMARY KEY, btree (id) INVALID REPLICA
IDENTITY
-Not null constraints:
+Not-null constraints:
"test_replica_identity4_id_not_null" NOT NULL "id"
Partitions: test_replica_identity4_1 FOR VALUES IN (1)
@@ -270,7 +270,7 @@ ALTER INDEX test_replica_identity4_pkey
Partition key: LIST (id)
Indexes:
"test_replica_identity4_pkey" PRIMARY KEY, btree (id) REPLICA IDENTITY
-Not null constraints:
+Not-null constraints:
"test_replica_identity4_id_not_null" NOT NULL "id"
Partitions: test_replica_identity4_1 FOR VALUES IN (1)
diff --git a/src/test/regress/expected/rowsecurity.out
b/src/test/regress/expected/rowsecurity.out
index 0e45c03d43..6988128aa4 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -955,7 +955,7 @@ Policies:
POLICY "pp1r" AS RESTRICTIVE
TO regress_rls_dave
USING ((cid < 55))
-Not null constraints:
+Not-null constraints:
"part_document_dlevel_not_null" NOT NULL "dlevel"
Partitions: part_document_fiction FOR VALUES FROM (11) TO (12),
part_document_nonfiction FOR VALUES FROM (99) TO (100),
--
2.41.0
From e5a304b2008e34a4386f5896d3a702aa4b71b33a Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Wed, 16 Aug 2023 10:46:23 +0200
Subject: [PATCH 2/2] Update ddl.sgml for named not-null constraints
---
doc/src/sgml/ddl.sgml | 55 ++++++++++++++++++++++++++++++++++---------
1 file changed, 44 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 58aaa691c6..bf331cafd5 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -651,17 +651,38 @@ <title>Not-Null Constraints</title>
price numeric
);
</programlisting>
+ An explicit constraint name can also be specified, for example:
+<programlisting>
+CREATE TABLE products (
+ product_no integer NOT NULL,
+ name text <emphasis>CONSTRAINT products_name_not_null</emphasis> NOT NULL,
+ price numeric
+);
+</programlisting>
+ </para>
+
+ <para>
+ A not-null constraint is usually written as a column constraint. The
+ syntax for writing it as a table constraint is
+<programlisting>
+CREATE TABLE products (
+ product_no integer,
+ name text,
+ price numeric,
+ <emphasis>NOT NULL product_no</emphasis>,
+ <emphasis>NOT NULL name</emphasis>
+);
+</programlisting>
+ But this syntax is not standard and mainly intended for use by
+ <application>pg_dump</application>.
</para>
<para>
- A not-null constraint is always written as a column constraint. A
- not-null constraint is functionally equivalent to creating a check
+ A not-null constraint is functionally equivalent to creating a check
constraint <literal>CHECK (<replaceable>column_name</replaceable>
IS NOT NULL)</literal>, but in
<productname>PostgreSQL</productname> creating an explicit
- not-null constraint is more efficient. The drawback is that you
- cannot give explicit names to not-null constraints created this
- way.
+ not-null constraint is more efficient.
</para>
<para>
@@ -678,6 +699,10 @@ <title>Not-Null Constraints</title>
order the constraints are checked.
</para>
+ <para>
+ However, a column can have at most one explicit not-null constraint.
+ </para>
+
<para>
The <literal>NOT NULL</literal> constraint has an inverse: the
<literal>NULL</literal> constraint. This does not mean that the
@@ -871,7 +896,7 @@ <title>Primary Keys</title>
<para>
A table can have at most one primary key. (There can be any number
- of unique and not-null constraints, which are functionally almost the
+ of unique constraints, which combined with not-null constraints are
functionally almost the
same thing, but only one can be identified as the primary key.)
Relational database theory
dictates that every table must have a primary key. This rule is
@@ -1531,11 +1556,16 @@ <title>Adding a Constraint</title>
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES
product_groups;
</programlisting>
- To add a not-null constraint, which cannot be written as a table
- constraint, use this syntax:
+ </para>
+
+ <para>
+ To add a not-null constraint, which is normally not written as a table
+ constraint, this special syntax is available:
<programlisting>
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;
</programlisting>
+ Unlike the <literal>ADD</literal> syntax above, this command silently does
+ nothing if the column already has a not-null constraint.
</para>
<para>
@@ -1576,12 +1606,15 @@ <title>Removing a Constraint</title>
</para>
<para>
- This works the same for all constraint types except not-null
- constraints. To drop a not null constraint use:
+ Simplified syntax is available to drop a not-null constraint:
<programlisting>
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
</programlisting>
- (Recall that not-null constraints do not have names.)
+ This mirrors the <literal>SET NOT NULL</literal> syntax for adding a
+ not-null constraints. This command will silently do nothing if the column
+ does not have a not-null constraint. (Recall that a column can have at
+ most one not-null constraint, so it is never ambigous which constraint
+ this command acts on.)
</para>
</sect2>
--
2.41.0
