psql-slashDplus-partition-indentation.diff
application/octet-stream
Filename: psql-slashDplus-partition-indentation.diff
Type: application/octet-stream
Part: 0
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: unified
| File | + | − |
|---|---|---|
| src/bin/psql/describe.c | 88 | 19 |
| src/test/regress/expected/cluster.out | 4 | 5 |
| src/test/regress/expected/create_table.out | 25 | 26 |
| src/test/regress/expected/foreign_data.out | 17 | 107 |
| src/test/regress/expected/indexing.out | 27 | 89 |
| src/test/regress/expected/insert.out | 10 | 1 |
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index bac94a338c..73c7f39d73 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3420,31 +3420,100 @@ describeOneTableDetails(const char *schemaname,
/* print child tables (with additional info if partitions) */
if (pset.sversion >= 140000)
printfPQExpBuffer(&buf,
- "SELECT c.oid::pg_catalog.regclass, c.relkind,"
- " inhdetachpending,"
- " pg_catalog.pg_get_expr(c.relpartbound, c.oid)\n"
- "FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n"
- "WHERE c.oid = i.inhrelid AND i.inhparent = '%s'\n"
- "ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT',"
- " c.oid::pg_catalog.regclass::pg_catalog.text;",
+ "WITH RECURSIVE partition_tree AS ( "
+ " SELECT c.oid::pg_catalog.regclass AS object_name, "
+ " c.oid,"
+ " 0 AS level,"
+ " c.relkind,"
+ " i.inhdetachpending,"
+ " pg_get_expr(c.relpartbound, c.oid) AS relpartbound,"
+ " array[c.relname::text] AS path \n"
+ " FROM pg_catalog.pg_class c \n"
+ " JOIN pg_catalog.pg_inherits i ON c.oid = i.inhrelid \n"
+ " WHERE i.inhparent ='%s' \n"
+ " UNION ALL \n"
+ " SELECT c.oid::pg_catalog.regclass AS object_name,"
+ " c.oid,"
+ " pt.level + 1 AS level,"
+ " c.relkind,"
+ " i.inhdetachpending,"
+ " pg_get_expr(c.relpartbound, c.oid) AS relpartbound,"
+ " pt.path || c.relname::text \n"
+ " FROM pg_catalog.pg_class c \n"
+ " JOIN pg_catalog.pg_inherits i ON c.oid = i.inhrelid \n"
+ " JOIN partition_tree pt ON i.inhparent = pt.oid \n"
+ " WHERE pt.level < 1) \n"
+ " SELECT REPEAT(E'\t', level) || object_name AS oid,"
+ " relkind,"
+ " inhdetachpending,"
+ " relpartbound as pg_get_expr \n"
+ "FROM partition_tree t \n"
+ "ORDER BY path, array_to_string(t.path || array[t.object_name::text], '.'), t.object_name, relpartbound = 'DEFAULT';",
+
oid);
else if (pset.sversion >= 100000)
printfPQExpBuffer(&buf,
- "SELECT c.oid::pg_catalog.regclass, c.relkind,"
- " false AS inhdetachpending,"
- " pg_catalog.pg_get_expr(c.relpartbound, c.oid)\n"
- "FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n"
- "WHERE c.oid = i.inhrelid AND i.inhparent = '%s'\n"
- "ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT',"
- " c.oid::pg_catalog.regclass::pg_catalog.text;",
+ "WITH RECURSIVE partition_tree AS (\n"
+ " SELECT c.oid::pg_catalog.regclass AS partition_name,"
+ " c.oid,"
+ " 0 AS level,"
+ " c.relkind,"
+ " false AS i.inhdetachpending,"
+ " pg_catalog.pg_get_expr(c.relpartbound, c.oid) AS relpartbound"
+ " array[c.relname::text] AS path \n"
+ " FROM pg_catalog.pg_class c "
+ " JOIN pg_catalog.pg_inherits i ON c.oid = i.inhrelid\n"
+ " WHERE i.inhparent = '%s'\n"
+ " UNION ALL"
+ " SELECT c.oid::pg_catalog.regclass AS partition_name,"
+ " c.oid,"
+ " pt.level + 1 AS level,"
+ " c.relkind,"
+ " false AS i.inhdetachpending,"
+ " pg_catalog.pg_get_expr(c.relpartbound, c.oid) AS relpartbound"
+ " pt.path || c.relname::text\n"
+ " FROM pg_catalog.pg_class c"
+ " JOIN pg_catalog.pg_inherits i ON c.oid = i.inhrelid"
+ " JOIN partition_tree pt ON i.inhparent = pt.oid"
+ " WHERE pt.level < 1 \n"
+ ")\n"
+ "SELECT REPEAT(' ', level * 4) || partition_name AS oid,"
+ " relkind,"
+ " inhdetachpending,"
+ " relpartbound as pg_get_expr\n"
+ "FROM partition_tree\n"
+ "ORDER BY partition_name::pg_catalog.text, level, relpartbound = 'DEFAULT';",
oid);
else
printfPQExpBuffer(&buf,
- "SELECT c.oid::pg_catalog.regclass, c.relkind,"
- " false AS inhdetachpending, NULL\n"
- "FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n"
- "WHERE c.oid = i.inhrelid AND i.inhparent = '%s'\n"
- "ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;",
+ "WITH RECURSIVE partition_tree AS (\n"
+ " SELECT c.oid::pg_catalog.regclass AS partition_name,"
+ " c.oid,"
+ " 0 AS level,"
+ " c.relkind,"
+ " false AS i.inhdetachpending,"
+ " array[c.relname::text] AS path \n"
+ " FROM pg_catalog.pg_class c "
+ " JOIN pg_catalog.pg_inherits i ON c.oid = i.inhrelid\n"
+ " WHERE i.inhparent = '%s'\n"
+ " UNION ALL"
+ " SELECT c.oid::pg_catalog.regclass AS partition_name,"
+ " c.oid,"
+ " pt.level + 1 AS level,"
+ " c.relkind,"
+ " false AS i.inhdetachpending,"
+ " pt.path || c.relname::text\n"
+ " FROM pg_catalog.pg_class c"
+ " JOIN pg_catalog.pg_inherits i ON c.oid = i.inhrelid"
+ " JOIN partition_tree pt ON i.inhparent = pt.oid"
+ " WHERE pt.level < 1 \n"
+ ")\n"
+ "SELECT REPEAT(' ', level * 4) || partition_name AS oid,"
+ " relkind,"
+ " inhdetachpending,"
+ " NULL\n"
+ "FROM partition_tree\n"
+ "ORDER BY partition_name::pg_catalog.text, level, relpartbound = 'DEFAULT';",
oid);
result = PSQLexec(buf.data);
diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out
index a666d89ef5..c5a55d60e2 100644
--- a/src/test/regress/expected/cluster.out
+++ b/src/test/regress/expected/cluster.out
@@ -247,12 +247,11 @@ ERROR: insert or update on table "clstr_tst" violates foreign key constraint "c
DETAIL: Key (b)=(1111) is not present in table "clstr_tst_s".
SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass
ORDER BY 1;
- conname
-----------------------
- clstr_tst_a_not_null
+ conname
+----------------
clstr_tst_con
clstr_tst_pkey
-(3 rows)
+(2 rows)
SELECT relname, relkind,
EXISTS(SELECT 1 FROM pg_class WHERE oid = c.reltoastrelid) AS hastoast
@@ -485,7 +484,7 @@ SELECT relname, old.level, old.relkind, old.relfilenode = new.relfilenode FROM o
Partition key: RANGE (a)
Indexes:
"clstrpart_idx" btree (a)
-Number of partitions: 3 (Use \d+ to list them.)
+Number of partitions: 6 (Use \d+ to list them.)
CLUSTER clstrpart;
ERROR: there is no previously clustered index for table "clstrpart"
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 344d05233a..e2d5d379aa 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -153,6 +153,14 @@ CREATE TABLE partitioned (
a2 int
) PARTITION BY LIST (a1, a2); -- fail
ERROR: cannot use "list" partition strategy with more than one column
+-- unsupported constraint type for partitioned tables
+CREATE TABLE partitioned (
+ a int,
+ EXCLUDE USING gist (a WITH &&)
+) PARTITION BY RANGE (a);
+ERROR: exclusion constraints are not supported on partitioned tables
+LINE 3: EXCLUDE USING gist (a WITH &&)
+ ^
-- prevent using prohibited expressions in the key
CREATE FUNCTION retset (a int) RETURNS SETOF int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE;
CREATE TABLE partitioned (
@@ -758,24 +766,22 @@ CREATE TABLE part_b PARTITION OF parted (
) FOR VALUES IN ('b');
NOTICE: merging constraint "check_a" with inherited definition
-- conislocal should be false for any merged constraints, true otherwise
-SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY coninhcount DESC, conname;
- conname | conislocal | coninhcount
--------------------+------------+-------------
- check_a | f | 1
- part_b_b_not_null | t | 1
- check_b | t | 0
-(3 rows)
+SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY conislocal, coninhcount;
+ conislocal | coninhcount
+------------+-------------
+ f | 1
+ t | 0
+(2 rows)
-- Once check_b is added to the parent, it should be made non-local for part_b
ALTER TABLE parted ADD CONSTRAINT check_b CHECK (b >= 0);
NOTICE: merging constraint "check_b" with inherited definition
-SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY coninhcount DESC, conname;
- conname | conislocal | coninhcount
--------------------+------------+-------------
- check_a | f | 1
- check_b | f | 1
- part_b_b_not_null | t | 1
-(3 rows)
+SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass;
+ conislocal | coninhcount
+------------+-------------
+ f | 1
+ f | 1
+(2 rows)
-- Neither check_a nor check_b are droppable from part_b
ALTER TABLE part_b DROP CONSTRAINT check_a;
@@ -786,11 +792,10 @@ ERROR: cannot drop inherited constraint "check_b" of relation "part_b"
-- traditional inheritance where they will be left behind, because they would
-- be local constraints.
ALTER TABLE parted DROP CONSTRAINT check_a, DROP CONSTRAINT check_b;
-SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY coninhcount DESC, conname;
- conname | conislocal | coninhcount
--------------------+------------+-------------
- part_b_b_not_null | t | 1
-(1 row)
+SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass;
+ conislocal | coninhcount
+------------+-------------
+(0 rows)
-- specify PARTITION BY for a partition
CREATE TABLE fail_part_col_not_found PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (c);
@@ -854,8 +859,6 @@ 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:
- "part_b_b_not_null" NOT NULL "b" (local, inherited)
-- Both partition bound and partition key in describe output
\d+ part_c
@@ -867,8 +870,6 @@ 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:
- "part_c_b_not_null" NOT NULL "b" (local, inherited)
Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10)
-- a level-2 partition's constraint will include the parent's expressions
@@ -880,8 +881,6 @@ 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:
- "part_c_b_not_null" NOT NULL "b" (inherited)
-- Show partition count in the parent's describe output
-- Tempted to include \d+ output listing partitions with bound info but
@@ -894,7 +893,7 @@ Not-null constraints:
a | text | | |
b | integer | | not null | 0
Partition key: LIST (a)
-Number of partitions: 3 (Use \d+ to list them.)
+Number of partitions: 4 (Use \d+ to list them.)
\d hash_parted
Partitioned table "public.hash_parted"
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 1dfe23cc1e..9204262bd0 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -742,8 +742,6 @@ 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:
- "ft1_c1_not_null" NOT NULL "c1"
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -866,9 +864,6 @@ 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:
- "ft1_c1_not_null" NOT NULL "c1"
- "ft1_c6_not_null" NOT NULL "c6"
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -1409,8 +1404,6 @@ CREATE FOREIGN TABLE ft2 () INHERITS (fd_pt1)
c1 | integer | | not null | | plain | |
c2 | text | | | | extended | |
c3 | date | | | | plain | |
-Not-null constraints:
- "fd_pt1_c1_not_null" NOT NULL "c1"
Child tables: ft2, FOREIGN
\d+ ft2
@@ -1420,8 +1413,6 @@ Child tables: ft2, FOREIGN
c1 | integer | | not null | | | plain | |
c2 | text | | | | | extended | |
c3 | date | | | | | plain | |
-Not-null constraints:
- "fd_pt1_c1_not_null" NOT NULL "c1" (inherited)
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
@@ -1434,8 +1425,6 @@ DROP FOREIGN TABLE ft2;
c1 | integer | | not null | | plain | |
c2 | text | | | | extended | |
c3 | date | | | | plain | |
-Not-null constraints:
- "fd_pt1_c1_not_null" NOT NULL "c1"
CREATE FOREIGN TABLE ft2 (
c1 integer NOT NULL,
@@ -1449,8 +1438,6 @@ CREATE FOREIGN TABLE ft2 (
c1 | integer | | not null | | | plain | |
c2 | text | | | | | extended | |
c3 | date | | | | | plain | |
-Not-null constraints:
- "ft2_c1_not_null" NOT NULL "c1"
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -1462,8 +1449,6 @@ ALTER FOREIGN TABLE ft2 INHERIT fd_pt1;
c1 | integer | | not null | | plain | |
c2 | text | | | | extended | |
c3 | date | | | | plain | |
-Not-null constraints:
- "fd_pt1_c1_not_null" NOT NULL "c1"
Child tables: ft2, FOREIGN
\d+ ft2
@@ -1473,8 +1458,6 @@ Child tables: ft2, FOREIGN
c1 | integer | | not null | | | plain | |
c2 | text | | | | | extended | |
c3 | date | | | | | plain | |
-Not-null constraints:
- "ft2_c1_not_null" NOT NULL "c1" (local, inherited)
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
@@ -1496,8 +1479,6 @@ NOTICE: merging column "c3" with inherited definition
c1 | integer | | not null | | | plain | |
c2 | text | | | | | extended | |
c3 | date | | | | | plain | |
-Not-null constraints:
- "ft2_c1_not_null" NOT NULL "c1" (local, inherited)
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
@@ -1511,8 +1492,6 @@ Child tables: ct3,
c1 | integer | | not null | | plain | |
c2 | text | | | | extended | |
c3 | date | | | | plain | |
-Not-null constraints:
- "ft2_c1_not_null" NOT NULL "c1" (inherited)
Inherits: ft2
\d+ ft3
@@ -1522,8 +1501,6 @@ Inherits: ft2
c1 | integer | | not null | | | plain | |
c2 | text | | | | | extended | |
c3 | date | | | | | plain | |
-Not-null constraints:
- "ft3_c1_not_null" NOT NULL "c1" (local, inherited)
Server: s0
Inherits: ft2
@@ -1545,10 +1522,9 @@ ALTER TABLE fd_pt1 ADD COLUMN c8 integer;
c6 | integer | | | | plain | |
c7 | integer | | not null | | plain | |
c8 | integer | | | | plain | |
-Not-null constraints:
- "fd_pt1_c1_not_null" NOT NULL "c1"
- "fd_pt1_c7_not_null" NOT NULL "c7"
-Child tables: ft2, FOREIGN
+Child tables: ft2, FOREIGN,
+ ct3,
+ ft3, FOREIGN
\d+ ft2
Foreign table "public.ft2"
@@ -1562,9 +1538,6 @@ Child tables: ft2, FOREIGN
c6 | integer | | | | | plain | |
c7 | integer | | not null | | | plain | |
c8 | integer | | | | | plain | |
-Not-null constraints:
- "ft2_c1_not_null" NOT NULL "c1" (local, inherited)
- "fd_pt1_c7_not_null" NOT NULL "c7" (inherited)
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
@@ -1583,9 +1556,6 @@ Child tables: ct3,
c6 | integer | | | | plain | |
c7 | integer | | not null | | plain | |
c8 | integer | | | | plain | |
-Not-null constraints:
- "ft2_c1_not_null" NOT NULL "c1" (inherited)
- "fd_pt1_c7_not_null" NOT NULL "c7" (inherited)
Inherits: ft2
\d+ ft3
@@ -1600,9 +1570,6 @@ Inherits: ft2
c6 | integer | | | | | plain | |
c7 | integer | | not null | | | plain | |
c8 | integer | | | | | plain | |
-Not-null constraints:
- "ft3_c1_not_null" NOT NULL "c1" (local, inherited)
- "fd_pt1_c7_not_null" NOT NULL "c7" (inherited)
Server: s0
Inherits: ft2
@@ -1631,10 +1598,9 @@ ALTER TABLE fd_pt1 ALTER COLUMN c8 SET STORAGE EXTERNAL;
c6 | integer | | not null | | plain | |
c7 | integer | | | | plain | |
c8 | text | | | | external | |
-Not-null constraints:
- "fd_pt1_c1_not_null" NOT NULL "c1"
- "fd_pt1_c6_not_null" NOT NULL "c6"
-Child tables: ft2, FOREIGN
+Child tables: ft2, FOREIGN,
+ ct3,
+ ft3, FOREIGN
\d+ ft2
Foreign table "public.ft2"
@@ -1648,9 +1614,6 @@ Child tables: ft2, FOREIGN
c6 | integer | | not null | | | plain | |
c7 | integer | | | | | plain | |
c8 | text | | | | | external | |
-Not-null constraints:
- "ft2_c1_not_null" NOT NULL "c1" (local, inherited)
- "fd_pt1_c6_not_null" NOT NULL "c6" (inherited)
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
@@ -1670,9 +1633,9 @@ ALTER TABLE fd_pt1 DROP COLUMN c8;
c1 | integer | | not null | | plain | 10000 |
c2 | text | | | | extended | |
c3 | date | | | | plain | |
-Not-null constraints:
- "fd_pt1_c1_not_null" NOT NULL "c1"
-Child tables: ft2, FOREIGN
+Child tables: ft2, FOREIGN,
+ ct3,
+ ft3, FOREIGN
\d+ ft2
Foreign table "public.ft2"
@@ -1681,8 +1644,6 @@ Child tables: ft2, FOREIGN
c1 | integer | | not null | | | plain | 10000 |
c2 | text | | | | | extended | |
c3 | date | | | | | plain | |
-Not-null constraints:
- "ft2_c1_not_null" NOT NULL "c1" (local, inherited)
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
@@ -1697,12 +1658,11 @@ SELECT relname, conname, contype, conislocal, coninhcount, connoinherit
FROM pg_class AS pc JOIN pg_constraint AS pgc ON (conrelid = pc.oid)
WHERE pc.relname = 'fd_pt1'
ORDER BY 1,2;
- relname | conname | contype | conislocal | coninhcount | connoinherit
----------+--------------------+---------+------------+-------------+--------------
- fd_pt1 | fd_pt1_c1_not_null | n | t | 0 | f
- fd_pt1 | fd_pt1chk1 | c | t | 0 | t
- fd_pt1 | fd_pt1chk2 | c | t | 0 | f
-(3 rows)
+ relname | conname | contype | conislocal | coninhcount | connoinherit
+---------+------------+---------+------------+-------------+--------------
+ fd_pt1 | fd_pt1chk1 | c | t | 0 | t
+ fd_pt1 | fd_pt1chk2 | c | t | 0 | f
+(2 rows)
-- child does not inherit NO INHERIT constraints
\d+ fd_pt1
@@ -1715,9 +1675,9 @@ SELECT relname, conname, contype, conislocal, coninhcount, connoinherit
Check constraints:
"fd_pt1chk1" CHECK (c1 > 0) NO INHERIT
"fd_pt1chk2" CHECK (c2 <> ''::text)
-Not-null constraints:
- "fd_pt1_c1_not_null" NOT NULL "c1"
-Child tables: ft2, FOREIGN
+Child tables: ft2, FOREIGN,
+ ct3,
+ ft3, FOREIGN
\d+ ft2
Foreign table "public.ft2"
@@ -1728,8 +1688,6 @@ Child tables: ft2, FOREIGN
c3 | date | | | | | plain | |
Check constraints:
"fd_pt1chk2" CHECK (c2 <> ''::text)
-Not-null constraints:
- "ft2_c1_not_null" NOT NULL "c1" (local, inherited)
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
@@ -1766,8 +1724,6 @@ ALTER FOREIGN TABLE ft2 INHERIT fd_pt1;
Check constraints:
"fd_pt1chk1" CHECK (c1 > 0) NO INHERIT
"fd_pt1chk2" CHECK (c2 <> ''::text)
-Not-null constraints:
- "fd_pt1_c1_not_null" NOT NULL "c1"
Child tables: ft2, FOREIGN
\d+ ft2
@@ -1779,8 +1735,6 @@ Child tables: ft2, FOREIGN
c3 | date | | | | | plain | |
Check constraints:
"fd_pt1chk2" CHECK (c2 <> ''::text)
-Not-null constraints:
- "ft2_c1_not_null" NOT NULL "c1" (local, inherited)
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
@@ -1800,8 +1754,6 @@ 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:
- "fd_pt1_c1_not_null" NOT NULL "c1"
Child tables: ft2, FOREIGN
\d+ ft2
@@ -1814,8 +1766,6 @@ Child tables: ft2, FOREIGN
Check constraints:
"fd_pt1chk2" CHECK (c2 <> ''::text)
"fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID
-Not-null constraints:
- "ft2_c1_not_null" NOT NULL "c1" (local, inherited)
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
@@ -1831,8 +1781,6 @@ ALTER TABLE fd_pt1 VALIDATE CONSTRAINT fd_pt1chk3;
c3 | date | | | | plain | |
Check constraints:
"fd_pt1chk3" CHECK (c2 <> ''::text)
-Not-null constraints:
- "fd_pt1_c1_not_null" NOT NULL "c1"
Child tables: ft2, FOREIGN
\d+ ft2
@@ -1845,8 +1793,6 @@ Child tables: ft2, FOREIGN
Check constraints:
"fd_pt1chk2" CHECK (c2 <> ''::text)
"fd_pt1chk3" CHECK (c2 <> ''::text)
-Not-null constraints:
- "ft2_c1_not_null" NOT NULL "c1" (local, inherited)
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
@@ -1866,8 +1812,6 @@ ALTER TABLE fd_pt1 RENAME CONSTRAINT fd_pt1chk3 TO f2_check;
f3 | date | | | | plain | |
Check constraints:
"f2_check" CHECK (f2 <> ''::text)
-Not-null constraints:
- "fd_pt1_c1_not_null" NOT NULL "f1"
Child tables: ft2, FOREIGN
\d+ ft2
@@ -1880,8 +1824,6 @@ Child tables: ft2, FOREIGN
Check constraints:
"f2_check" CHECK (f2 <> ''::text)
"fd_pt1chk2" CHECK (f2 <> ''::text)
-Not-null constraints:
- "ft2_c1_not_null" NOT NULL "f1" (local, inherited)
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
@@ -1928,8 +1870,6 @@ 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:
- "fd_pt2_c1_not_null" NOT NULL "c1"
Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN
\d+ fd_pt2_1
@@ -1941,8 +1881,6 @@ 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:
- "fd_pt2_c1_not_null" NOT NULL "c1" (inherited)
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -1962,8 +1900,6 @@ CREATE FOREIGN TABLE fd_pt2_1 (
c2 | text | | | | | extended | |
c3 | date | | | | | plain | |
c4 | character(1) | | | | | extended | |
-Not-null constraints:
- "fd_pt2_1_c1_not_null" NOT NULL "c1"
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -1979,8 +1915,6 @@ DROP FOREIGN TABLE fd_pt2_1;
c2 | text | | | | extended | |
c3 | date | | | | plain | |
Partition key: LIST (c1)
-Not-null constraints:
- "fd_pt2_c1_not_null" NOT NULL "c1"
Number of partitions: 0
CREATE FOREIGN TABLE fd_pt2_1 (
@@ -1995,8 +1929,6 @@ CREATE FOREIGN TABLE fd_pt2_1 (
c1 | integer | | not null | | | plain | |
c2 | text | | | | | extended | |
c3 | date | | | | | plain | |
-Not-null constraints:
- "fd_pt2_1_c1_not_null" NOT NULL "c1"
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -2010,8 +1942,6 @@ 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:
- "fd_pt2_c1_not_null" NOT NULL "c1"
Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN
\d+ fd_pt2_1
@@ -2023,8 +1953,6 @@ 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:
- "fd_pt2_1_c1_not_null" NOT NULL "c1" (inherited)
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -2042,8 +1970,6 @@ ALTER TABLE fd_pt2_1 ADD CONSTRAINT p21chk CHECK (c2 <> '');
c2 | text | | | | extended | |
c3 | date | | | | plain | |
Partition key: LIST (c1)
-Not-null constraints:
- "fd_pt2_c1_not_null" NOT NULL "c1"
Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN
\d+ fd_pt2_1
@@ -2057,9 +1983,6 @@ 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:
- "fd_pt2_1_c1_not_null" NOT NULL "c1" (inherited)
- "fd_pt2_1_c3_not_null" NOT NULL "c3"
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -2077,9 +2000,6 @@ ALTER TABLE fd_pt2 ALTER c2 SET NOT NULL;
c2 | text | | not null | | extended | |
c3 | date | | | | plain | |
Partition key: LIST (c1)
-Not-null constraints:
- "fd_pt2_c1_not_null" NOT NULL "c1"
- "fd_pt2_c2_not_null" NOT NULL "c2"
Number of partitions: 0
\d+ fd_pt2_1
@@ -2091,9 +2011,6 @@ Number of partitions: 0
c3 | date | | not null | | | plain | |
Check constraints:
"p21chk" CHECK (c2 <> ''::text)
-Not-null constraints:
- "fd_pt2_1_c1_not_null" NOT NULL "c1"
- "fd_pt2_1_c3_not_null" NOT NULL "c3"
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -2113,9 +2030,6 @@ 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:
- "fd_pt2_c1_not_null" NOT NULL "c1"
- "fd_pt2_c2_not_null" NOT NULL "c2"
Number of partitions: 0
\d+ fd_pt2_1
@@ -2127,10 +2041,6 @@ Number of partitions: 0
c3 | date | | not null | | | plain | |
Check constraints:
"p21chk" CHECK (c2 <> ''::text)
-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"
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index 087f955b1e..e73be96de3 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -986,32 +986,11 @@ DETAIL: PRIMARY KEY constraint on table "idxpart" lacks column "a" which is par
-- OK if you use them in some other order
create table idxpart (a int, b int, c text, primary key (a, b, c)) partition by range (b, c, a);
drop table idxpart;
--- OK to add an exclusion constraint if partitioning by its equal column
-create table idxpart (a int4range, exclude USING GIST (a with = )) partition by range (a);
-drop table idxpart;
--- OK more than one equal column
-create table idxpart (a int4range, b int4range, exclude USING GIST (a with =, b with =)) partition by range (a, b);
-drop table idxpart;
--- OK with more than one equal column: constraint is a proper superset of partition key
-create table idxpart (a int4range, b int4range, exclude USING GIST (a with =, b with =)) partition by range (a);
-drop table idxpart;
--- Not OK more than one equal column: partition keys are a proper superset of constraint
-create table idxpart (a int4range, b int4range, exclude USING GIST (a with = )) partition by range (a, b);
-ERROR: unique constraint on partitioned table must include all partitioning columns
-DETAIL: EXCLUDE constraint on table "idxpart" lacks column "b" which is part of the partition key.
--- Not OK with just -|-
-create table idxpart (a int4range, exclude USING GIST (a with -|- )) partition by range (a);
-ERROR: cannot match partition key to index on column "a" using non-equal operator "-|-"
--- OK with equals and &&, and equals is the partition key
-create table idxpart (a int4range, b int4range, exclude USING GIST (a with =, b with &&)) partition by range (a);
-drop table idxpart;
--- Not OK with equals and &&, and equals is not the partition key
-create table idxpart (a int4range, b int4range, c int4range, exclude USING GIST (b with =, c with &&)) partition by range (a);
-ERROR: unique constraint on partitioned table must include all partitioning columns
-DETAIL: EXCLUDE constraint on table "idxpart" lacks column "a" which is part of the partition key.
--- OK more than one equal column and a && column
-create table idxpart (a int4range, b int4range, c int4range, exclude USING GIST (a with =, b with =, c with &&)) partition by range (a, b);
-drop table idxpart;
+-- not other types of index-based constraints
+create table idxpart (a int, exclude (a with = )) partition by range (a);
+ERROR: exclusion constraints are not supported on partitioned tables
+LINE 1: create table idxpart (a int, exclude (a with = )) partition ...
+ ^
-- no expressions in partition key for PK/UNIQUE
create table idxpart (a int primary key, b int) partition by range ((b + a));
ERROR: unsupported PRIMARY KEY constraint with partition key definition
@@ -1068,42 +1047,12 @@ Indexes:
Number of partitions: 0
drop table idxpart;
--- Exclusion constraints can be added if partitioning by their equal column
-create table idxpart (a int4range, b int4range) partition by range (a);
-alter table idxpart add exclude USING GIST (a with =);
-drop table idxpart;
--- OK more than one equal column
-create table idxpart (a int4range, b int4range) partition by range (a, b);
-alter table idxpart add exclude USING GIST (a with =, b with =);
-drop table idxpart;
--- OK with more than one equal column: constraint is a proper superset of partition key
-create table idxpart (a int4range, b int4range) partition by range (a);
-alter table idxpart add exclude USING GIST (a with =, b with =);
-drop table idxpart;
--- Not OK more than one equal column: partition keys are a proper superset of constraint
-create table idxpart (a int4range, b int4range) partition by range (a, b);
-alter table idxpart add exclude USING GIST (a with =);
-ERROR: unique constraint on partitioned table must include all partitioning columns
-DETAIL: EXCLUDE constraint on table "idxpart" lacks column "b" which is part of the partition key.
-drop table idxpart;
--- Not OK with just -|-
-create table idxpart (a int4range, b int4range) partition by range (a, b);
-alter table idxpart add exclude USING GIST (a with -|-);
-ERROR: cannot match partition key to index on column "a" using non-equal operator "-|-"
-drop table idxpart;
--- OK with equals and &&, and equals is the partition key
-create table idxpart (a int4range, b int4range) partition by range (a);
-alter table idxpart add exclude USING GIST (a with =, b with &&);
-drop table idxpart;
--- Not OK with equals and &&, and equals is not the partition key
-create table idxpart (a int4range, b int4range, c int4range) partition by range (a);
-alter table idxpart add exclude USING GIST (b with =, c with &&);
-ERROR: unique constraint on partitioned table must include all partitioning columns
-DETAIL: EXCLUDE constraint on table "idxpart" lacks column "a" which is part of the partition key.
-drop table idxpart;
--- OK more than one equal column and a && column
-create table idxpart (a int4range, b int4range, c int4range) partition by range (a, b);
-alter table idxpart add exclude USING GIST (a with =, b with =, c with &&);
+-- Exclusion constraints cannot be added
+create table idxpart (a int, b int) partition by range (a);
+alter table idxpart add exclude (a with =);
+ERROR: exclusion constraints are not supported on partitioned tables
+LINE 1: alter table idxpart add exclude (a with =);
+ ^
drop table idxpart;
-- When (sub)partitions are created, they also contain the constraint
create table idxpart (a int, b int, primary key (a, b)) partition by range (a, b);
@@ -1116,18 +1065,16 @@ create table idxpart3 (b int not null, a int not null);
alter table idxpart attach partition idxpart3 for values from (20, 20) to (30, 30);
select conname, contype, conrelid::regclass, conindid::regclass, conkey
from pg_constraint where conrelid::regclass::text like 'idxpart%'
- order by conrelid::regclass::text, conname;
- conname | contype | conrelid | conindid | conkey
----------------------+---------+-----------+----------------+--------
- idxpart_pkey | p | idxpart | idxpart_pkey | {1,2}
- idxpart1_pkey | p | idxpart1 | idxpart1_pkey | {1,2}
- idxpart2_pkey | p | idxpart2 | idxpart2_pkey | {1,2}
- idxpart21_pkey | p | idxpart21 | idxpart21_pkey | {1,2}
- idxpart22_pkey | p | idxpart22 | idxpart22_pkey | {1,2}
- idxpart3_a_not_null | n | idxpart3 | - | {2}
- idxpart3_b_not_null | n | idxpart3 | - | {1}
- idxpart3_pkey | p | idxpart3 | idxpart3_pkey | {2,1}
-(8 rows)
+ order by conname;
+ conname | contype | conrelid | conindid | conkey
+----------------+---------+-----------+----------------+--------
+ idxpart1_pkey | p | idxpart1 | idxpart1_pkey | {1,2}
+ idxpart21_pkey | p | idxpart21 | idxpart21_pkey | {1,2}
+ idxpart22_pkey | p | idxpart22 | idxpart22_pkey | {1,2}
+ idxpart2_pkey | p | idxpart2 | idxpart2_pkey | {1,2}
+ idxpart3_pkey | p | idxpart3 | idxpart3_pkey | {2,1}
+ idxpart_pkey | p | idxpart | idxpart_pkey | {1,2}
+(6 rows)
drop table idxpart;
-- Verify that multi-layer partitioning honors the requirement that all
@@ -1260,21 +1207,12 @@ create table idxpart (a int) partition by range (a);
create table idxpart0 (like idxpart);
alter table idxpart0 add unique (a);
alter table idxpart attach partition idxpart0 default;
-alter table only idxpart add primary key (a); -- works, but idxpart0.a is nullable
-\d idxpart0
- Table "public.idxpart0"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
- a | integer | | |
-Partition of: idxpart DEFAULT
-Indexes:
- "idxpart0_a_key" UNIQUE CONSTRAINT, btree (a)
-
-alter index idxpart_pkey attach partition idxpart0_a_key; -- fails, lacks NOT NULL
-ERROR: invalid primary key definition
-DETAIL: Column "a" of relation "idxpart0" is not marked NOT NULL.
+alter table only idxpart add primary key (a); -- fail, no NOT NULL constraint
+ERROR: constraint must be added to child tables too
+DETAIL: Column "a" of relation "idxpart0" is not already NOT NULL.
+HINT: Do not specify the ONLY keyword.
alter table idxpart0 alter column a set not null;
-alter index idxpart_pkey attach partition idxpart0_a_key;
+alter table only idxpart add primary key (a); -- now it works
alter table idxpart0 alter column a drop not null; -- fail, pkey needs it
ERROR: column "a" is marked NOT NULL in parent table
drop table idxpart;
@@ -1446,7 +1384,7 @@ alter table parted_index_col_drop drop column c;
Partition key: LIST (a)
Indexes:
"parted_index_col_drop_b_idx" btree (b)
-Number of partitions: 2 (Use \d+ to list them.)
+Number of partitions: 3 (Use \d+ to list them.)
\d parted_index_col_drop1
Partitioned table "public.parted_index_col_drop1"
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index dd4354fc7d..fd4a7b008b 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -455,11 +455,20 @@ from hash_parted order by part;
Partition key: LIST (lower(a))
Partitions: part_aa_bb FOR VALUES IN ('aa', 'bb'),
part_cc_dd FOR VALUES IN ('cc', 'dd'),
+ part_default DEFAULT, PARTITIONED,
+ part_default_p1 FOR VALUES FROM (20) TO (30),
+ part_default_p2 FOR VALUES FROM (30) TO (40),
part_ee_ff FOR VALUES IN ('ee', 'ff'), PARTITIONED,
+ part_ee_ff1 FOR VALUES FROM (1) TO (10),
+ part_ee_ff2 FOR VALUES FROM (10) TO (20),
+ part_ee_ff3 FOR VALUES FROM (20) TO (30), PARTITIONED,
part_gg FOR VALUES IN ('gg'), PARTITIONED,
+ part_gg1 FOR VALUES FROM (MINVALUE) TO (1),
+ part_gg2 FOR VALUES FROM (1) TO (10), PARTITIONED,
part_null FOR VALUES IN (NULL),
part_xx_yy FOR VALUES IN ('xx', 'yy'), PARTITIONED,
- part_default DEFAULT, PARTITIONED
+ part_xx_yy_defpart DEFAULT,
+ part_xx_yy_p1 FOR VALUES IN ('xx')
-- cleanup
drop table range_parted, list_parted;