aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--contrib/btree_gist/Makefile2
-rw-r--r--contrib/btree_gist/expected/partitions.out82
-rw-r--r--contrib/btree_gist/meson.build1
-rw-r--r--contrib/btree_gist/sql/partitions.sql39
-rw-r--r--doc/src/sgml/ddl.sgml12
-rw-r--r--src/backend/commands/indexcmds.c59
-rw-r--r--src/backend/parser/parse_utilcmd.c6
-rw-r--r--src/test/regress/expected/alter_table.out7
-rw-r--r--src/test/regress/expected/create_table.out8
-rw-r--r--src/test/regress/expected/indexing.out73
-rw-r--r--src/test/regress/sql/alter_table.sql5
-rw-r--r--src/test/regress/sql/create_table.sql6
-rw-r--r--src/test/regress/sql/indexing.sql57
13 files changed, 284 insertions, 73 deletions
diff --git a/contrib/btree_gist/Makefile b/contrib/btree_gist/Makefile
index 48997c75f63..073dcc745c4 100644
--- a/contrib/btree_gist/Makefile
+++ b/contrib/btree_gist/Makefile
@@ -38,7 +38,7 @@ PGFILEDESC = "btree_gist - B-tree equivalent GiST operator classes"
REGRESS = init int2 int4 int8 float4 float8 cash oid timestamp timestamptz \
time timetz date interval macaddr macaddr8 inet cidr text varchar char \
- bytea bit varbit numeric uuid not_equal enum bool
+ bytea bit varbit numeric uuid not_equal enum bool partitions
SHLIB_LINK += $(filter -lm, $(LIBS))
diff --git a/contrib/btree_gist/expected/partitions.out b/contrib/btree_gist/expected/partitions.out
new file mode 100644
index 00000000000..5bddb915871
--- /dev/null
+++ b/contrib/btree_gist/expected/partitions.out
@@ -0,0 +1,82 @@
+-- Make sure we can create an exclusion constraint
+-- across a partitioned table.
+-- That code looks at strategy numbers that can differ in regular gist vs btree_gist,
+-- so we want to make sure it works here too.
+create table parttmp (
+ id int,
+ valid_at daterange,
+ exclude using gist (id with =, valid_at with &&)
+) partition by range (id);
+create table parttmp_1_to_10 partition of parttmp for values from (1) to (10);
+create table parttmp_11_to_20 partition of parttmp for values from (11) to (20);
+insert into parttmp (id, valid_at) values
+ (1, '[2000-01-01, 2000-02-01)'),
+ (1, '[2000-02-01, 2000-03-01)'),
+ (2, '[2000-01-01, 2000-02-01)'),
+ (11, '[2000-01-01, 2000-02-01)'),
+ (11, '[2000-02-01, 2000-03-01)'),
+ (12, '[2000-01-01, 2000-02-01)');
+select * from parttmp order by id, valid_at;
+ id | valid_at
+----+-------------------------
+ 1 | [01-01-2000,02-01-2000)
+ 1 | [02-01-2000,03-01-2000)
+ 2 | [01-01-2000,02-01-2000)
+ 11 | [01-01-2000,02-01-2000)
+ 11 | [02-01-2000,03-01-2000)
+ 12 | [01-01-2000,02-01-2000)
+(6 rows)
+
+select * from parttmp_1_to_10 order by id, valid_at;
+ id | valid_at
+----+-------------------------
+ 1 | [01-01-2000,02-01-2000)
+ 1 | [02-01-2000,03-01-2000)
+ 2 | [01-01-2000,02-01-2000)
+(3 rows)
+
+select * from parttmp_11_to_20 order by id, valid_at;
+ id | valid_at
+----+-------------------------
+ 11 | [01-01-2000,02-01-2000)
+ 11 | [02-01-2000,03-01-2000)
+ 12 | [01-01-2000,02-01-2000)
+(3 rows)
+
+update parttmp set valid_at = valid_at * '[2000-01-15,2000-02-15)' where id = 1;
+select * from parttmp order by id, valid_at;
+ id | valid_at
+----+-------------------------
+ 1 | [01-15-2000,02-01-2000)
+ 1 | [02-01-2000,02-15-2000)
+ 2 | [01-01-2000,02-01-2000)
+ 11 | [01-01-2000,02-01-2000)
+ 11 | [02-01-2000,03-01-2000)
+ 12 | [01-01-2000,02-01-2000)
+(6 rows)
+
+select * from parttmp_1_to_10 order by id, valid_at;
+ id | valid_at
+----+-------------------------
+ 1 | [01-15-2000,02-01-2000)
+ 1 | [02-01-2000,02-15-2000)
+ 2 | [01-01-2000,02-01-2000)
+(3 rows)
+
+select * from parttmp_11_to_20 order by id, valid_at;
+ id | valid_at
+----+-------------------------
+ 11 | [01-01-2000,02-01-2000)
+ 11 | [02-01-2000,03-01-2000)
+ 12 | [01-01-2000,02-01-2000)
+(3 rows)
+
+-- make sure the excluson constraint excludes:
+insert into parttmp (id, valid_at) values
+ (2, '[2000-01-15, 2000-02-01)');
+ERROR: conflicting key value violates exclusion constraint "parttmp_1_to_10_id_valid_at_excl"
+DETAIL: Key (id, valid_at)=(2, [01-15-2000,02-01-2000)) conflicts with existing key (id, valid_at)=(2, [01-01-2000,02-01-2000)).
+drop table parttmp;
+-- should fail with a good error message:
+create table parttmp (id int, valid_at daterange, exclude using gist (id with <>, valid_at with &&)) partition by range (id);
+ERROR: cannot match partition key to index on column "id" using non-equal operator "<>"
diff --git a/contrib/btree_gist/meson.build b/contrib/btree_gist/meson.build
index 5811026301a..087c5b8d4bf 100644
--- a/contrib/btree_gist/meson.build
+++ b/contrib/btree_gist/meson.build
@@ -88,6 +88,7 @@ tests += {
'not_equal',
'enum',
'bool',
+ 'partitions',
],
},
}
diff --git a/contrib/btree_gist/sql/partitions.sql b/contrib/btree_gist/sql/partitions.sql
new file mode 100644
index 00000000000..6265c10121f
--- /dev/null
+++ b/contrib/btree_gist/sql/partitions.sql
@@ -0,0 +1,39 @@
+-- Make sure we can create an exclusion constraint
+-- across a partitioned table.
+-- That code looks at strategy numbers that can differ in regular gist vs btree_gist,
+-- so we want to make sure it works here too.
+create table parttmp (
+ id int,
+ valid_at daterange,
+ exclude using gist (id with =, valid_at with &&)
+) partition by range (id);
+
+create table parttmp_1_to_10 partition of parttmp for values from (1) to (10);
+create table parttmp_11_to_20 partition of parttmp for values from (11) to (20);
+
+insert into parttmp (id, valid_at) values
+ (1, '[2000-01-01, 2000-02-01)'),
+ (1, '[2000-02-01, 2000-03-01)'),
+ (2, '[2000-01-01, 2000-02-01)'),
+ (11, '[2000-01-01, 2000-02-01)'),
+ (11, '[2000-02-01, 2000-03-01)'),
+ (12, '[2000-01-01, 2000-02-01)');
+
+select * from parttmp order by id, valid_at;
+select * from parttmp_1_to_10 order by id, valid_at;
+select * from parttmp_11_to_20 order by id, valid_at;
+
+update parttmp set valid_at = valid_at * '[2000-01-15,2000-02-15)' where id = 1;
+
+select * from parttmp order by id, valid_at;
+select * from parttmp_1_to_10 order by id, valid_at;
+select * from parttmp_11_to_20 order by id, valid_at;
+
+-- make sure the excluson constraint excludes:
+insert into parttmp (id, valid_at) values
+ (2, '[2000-01-15, 2000-02-01)');
+
+drop table parttmp;
+
+-- should fail with a good error message:
+create table parttmp (id int, valid_at daterange, exclude using gist (id with <>, valid_at with &&)) partition by range (id);
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 43179959658..58aaa691c6a 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -4216,11 +4216,13 @@ ALTER INDEX measurement_city_id_logdate_key
<listitem>
<para>
- There is no way to create an exclusion constraint spanning the
- whole partitioned table. It is only possible to put such a
- constraint on each leaf partition individually. Again, this
- limitation stems from not being able to enforce cross-partition
- restrictions.
+ Similarly an exclusion constraint must include all the
+ partition key columns. Furthermore the constraint must compare those
+ columns for equality (not e.g. <literal>&amp;&amp;</literal>).
+ Again, this limitation stems from not being able to enforce
+ cross-partition restrictions. The constraint may include additional
+ columns that aren't part of the partition key, and it may compare
+ those with any operators you like.
</para>
</listitem>
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 02250ae74be..baf3e6e57a5 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -712,11 +712,6 @@ DefineIndex(Oid relationId,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot create index on partitioned table \"%s\" concurrently",
RelationGetRelationName(rel))));
- if (stmt->excludeOpNames)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot create exclusion constraints on partitioned table \"%s\"",
- RelationGetRelationName(rel))));
}
/*
@@ -923,15 +918,16 @@ DefineIndex(Oid relationId,
index_check_primary_key(rel, indexInfo, is_alter_table, stmt);
/*
- * If this table is partitioned and we're creating a unique index or a
- * primary key, make sure that the partition key is a subset of the
- * index's columns. Otherwise it would be possible to violate uniqueness
- * by putting values that ought to be unique in different partitions.
+ * If this table is partitioned and we're creating a unique index, primary
+ * key, or exclusion constraint, make sure that the partition key is a
+ * subset of the index's columns. Otherwise it would be possible to
+ * violate uniqueness by putting values that ought to be unique in
+ * different partitions.
*
* We could lift this limitation if we had global indexes, but those have
* their own problems, so this is a useful feature combination.
*/
- if (partitioned && (stmt->unique || stmt->primary))
+ if (partitioned && (stmt->unique || stmt->excludeOpNames))
{
PartitionKey key = RelationGetPartitionKey(rel);
const char *constraint_type;
@@ -941,7 +937,7 @@ DefineIndex(Oid relationId,
constraint_type = "PRIMARY KEY";
else if (stmt->unique)
constraint_type = "UNIQUE";
- else if (stmt->excludeOpNames != NIL)
+ else if (stmt->excludeOpNames)
constraint_type = "EXCLUDE";
else
{
@@ -984,11 +980,11 @@ DefineIndex(Oid relationId,
* We'll need to be able to identify the equality operators
* associated with index columns, too. We know what to do with
* btree opclasses; if there are ever any other index types that
- * support unique indexes, this logic will need extension.
+ * support unique indexes, this logic will need extension. But if
+ * we have an exclusion constraint, it already knows the
+ * operators, so we don't have to infer them.
*/
- if (accessMethodId == BTREE_AM_OID)
- eq_strategy = BTEqualStrategyNumber;
- else
+ if (stmt->unique && accessMethodId != BTREE_AM_OID)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot match partition key to an index using access method \"%s\"",
@@ -1019,17 +1015,38 @@ DefineIndex(Oid relationId,
&idx_opfamily,
&idx_opcintype))
{
- Oid idx_eqop;
+ Oid idx_eqop = InvalidOid;
+
+ if (stmt->unique)
+ idx_eqop = get_opfamily_member(idx_opfamily,
+ idx_opcintype,
+ idx_opcintype,
+ BTEqualStrategyNumber);
+ else if (stmt->excludeOpNames)
+ idx_eqop = indexInfo->ii_ExclusionOps[j];
+ Assert(idx_eqop);
- idx_eqop = get_opfamily_member(idx_opfamily,
- idx_opcintype,
- idx_opcintype,
- eq_strategy);
if (ptkey_eqop == idx_eqop)
{
found = true;
break;
}
+ else if (stmt->excludeOpNames)
+ {
+ /*
+ * We found a match, but it's not an equality
+ * operator. Instead of failing below with an
+ * error message about a missing column, fail now
+ * and explain that the operator is wrong.
+ */
+ Form_pg_attribute att = TupleDescAttr(RelationGetDescr(rel), key->partattrs[i] - 1);
+
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot match partition key to index on column \"%s\" using non-equal operator \"%s\"",
+ NameStr(att->attname),
+ get_opname(indexInfo->ii_ExclusionOps[j]))));
+ }
}
}
}
@@ -1101,7 +1118,7 @@ DefineIndex(Oid relationId,
constraint_type = "PRIMARY KEY";
else if (stmt->unique)
constraint_type = "UNIQUE";
- else if (stmt->excludeOpNames != NIL)
+ else if (stmt->excludeOpNames)
constraint_type = "EXCLUDE";
else
{
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index d67580fc77a..e48e9e99d36 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -900,12 +900,6 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
errmsg("exclusion constraints are not supported on foreign tables"),
parser_errposition(cxt->pstate,
constraint->location)));
- if (cxt->ispartitioned)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("exclusion constraints are not supported on partitioned tables"),
- parser_errposition(cxt->pstate,
- constraint->location)));
cxt->ixconstraints = lappend(cxt->ixconstraints, constraint);
break;
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 05351cb1a43..cd814ff321c 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -3834,16 +3834,11 @@ Referenced by:
TABLE "ataddindex" CONSTRAINT "ataddindex_ref_id_fkey" FOREIGN KEY (ref_id) REFERENCES ataddindex(id)
DROP TABLE ataddindex;
--- unsupported constraint types for partitioned tables
+-- cannot drop column that is part of the partition key
CREATE TABLE partitioned (
a int,
b int
) PARTITION BY RANGE (a, (a+b+1));
-ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
-ERROR: exclusion constraints are not supported on partitioned tables
-LINE 1: ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
- ^
--- cannot drop column that is part of the partition key
ALTER TABLE partitioned DROP COLUMN a;
ERROR: cannot drop column "a" because it is part of the partition key of relation "partitioned"
ALTER TABLE partitioned ALTER COLUMN a TYPE char(5);
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 1c3ef2b05a8..2a0902ece24 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -153,14 +153,6 @@ 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 (
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index 3e5645c2ab6..2be8ffa7ec4 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -986,11 +986,32 @@ 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;
--- 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 ...
- ^
+-- 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;
-- 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
@@ -1047,12 +1068,42 @@ Indexes:
Number of partitions: 0
drop table idxpart;
--- 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 =);
- ^
+-- 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 &&);
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);
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 58ea20ac3dc..ff8c4984191 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -2342,14 +2342,11 @@ ALTER TABLE ataddindex
\d ataddindex
DROP TABLE ataddindex;
--- unsupported constraint types for partitioned tables
+-- cannot drop column that is part of the partition key
CREATE TABLE partitioned (
a int,
b int
) PARTITION BY RANGE (a, (a+b+1));
-ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
-
--- cannot drop column that is part of the partition key
ALTER TABLE partitioned DROP COLUMN a;
ALTER TABLE partitioned ALTER COLUMN a TYPE char(5);
ALTER TABLE partitioned DROP COLUMN b;
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index 93ccf77d4a1..82ada476618 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -106,12 +106,6 @@ CREATE TABLE partitioned (
a2 int
) PARTITION BY LIST (a1, a2); -- fail
--- unsupported constraint type for partitioned tables
-CREATE TABLE partitioned (
- a int,
- EXCLUDE USING gist (a WITH &&)
-) PARTITION BY RANGE (a);
-
-- prevent using prohibited expressions in the key
CREATE FUNCTION retset (a int) RETURNS SETOF int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE;
CREATE TABLE partitioned (
diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql
index d6e5a06d95a..b69c41832ca 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -483,8 +483,27 @@ create table idxpart (a int, b int primary key) partition by range (b, a);
create table idxpart (a int, b int, c text, primary key (a, b, c)) partition by range (b, c, a);
drop table idxpart;
--- not other types of index-based constraints
-create table idxpart (a int, exclude (a with = )) partition by range (a);
+-- 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);
+-- Not OK with just -|-
+create table idxpart (a int4range, exclude USING GIST (a with -|- )) partition by range (a);
+-- 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);
+-- 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;
-- no expressions in partition key for PK/UNIQUE
create table idxpart (a int primary key, b int) partition by range ((b + a));
@@ -506,9 +525,37 @@ alter table idxpart add unique (b, a); -- this works
\d idxpart
drop table idxpart;
--- Exclusion constraints cannot be added
-create table idxpart (a int, b int) partition by range (a);
-alter table idxpart add exclude (a with =);
+-- 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 =);
+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 -|-);
+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 &&);
+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 &&);
drop table idxpart;
-- When (sub)partitions are created, they also contain the constraint
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-19 18:47:46 +0000