public inbox for [email protected]  

help / color / mirror / Atom feed

domain for WITHOUT OVERLAPS
11+ messages / 4 participants
[nested] [flat]

* domain for WITHOUT OVERLAPS
@ 2025-12-03 07:38 jian he <[email protected]>
  2025-12-03 18:39 ` Re: domain for WITHOUT OVERLAPS Paul A Jungwirth <[email protected]>
  0 siblings, 1 reply; 11+ messages in thread

From: jian he @ 2025-12-03 07:38 UTC (permalink / raw)
  To: pgsql-hackers

hi.

While working on domain IS JSON, I found out that
WITHOUT OVERLAPS does not support for domain too.
but it does support user-defined range types (via CREATE TYPE).

after looking around:
check_exclusion_or_unique_constraint->ExecWithoutOverlapsNotEmpty
ExecWithoutOverlapsNotEmpty typtype should be domain's basetype's typtype
otherwise it will fallback to:
            elog(ERROR, "WITHOUT OVERLAPS column \"%s\" is not a range
or multirange",
                 NameStr(attname));

That means we need to cheaply get the domain basetype's
pg_type.typtype in lookup_type_cache.
so I added a new char field: TypeCacheEntry.domainBaseTyptype.

it should be working:
For primary key, unique constraint: pg_index.indclass is
the same for domain and basetype.
no need to worry too much about pg_index.indcollation because range type is not
collateable.
For foreign key: pg_constraint (conpfeqop, conppeqop, conffeqop) are the same
for domain and basetype.


--
jian
https://www.enterprisedb.com/


Attachments:

  [text/x-patch] v1-0001-domain-for-WITHOUT-OVERLAPS.patch (10.4K, 2-v1-0001-domain-for-WITHOUT-OVERLAPS.patch)
  download | inline diff:
From 37852b4a14ae29e845e748be9a0e0499990e4d03 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Wed, 3 Dec 2025 15:31:03 +0800
Subject: [PATCH v1 1/1] domain for WITHOUT OVERLAPS

Domains are now supported for WITHOUT OVERLAPS primary key, unique, and foreign
key constraints.

discussion: https://postgr.es/m/
---
 src/backend/executor/execIndexing.c           | 11 +++-
 src/backend/parser/parse_utilcmd.c            |  2 +
 src/backend/utils/cache/typcache.c            |  2 +
 src/include/utils/typcache.h                  |  5 +-
 .../regress/expected/without_overlaps.out     | 63 ++++++++++++++++++-
 src/test/regress/sql/without_overlaps.sql     | 47 +++++++++++++-
 6 files changed, 122 insertions(+), 8 deletions(-)

diff --git a/src/backend/executor/execIndexing.c b/src/backend/executor/execIndexing.c
index dd323c9b9fd..fcf30c6cd2f 100644
--- a/src/backend/executor/execIndexing.c
+++ b/src/backend/executor/execIndexing.c
@@ -752,13 +752,20 @@ check_exclusion_or_unique_constraint(Relation heap, Relation index,
 
 		if (!isnull[indnkeyatts - 1])
 		{
+			char 		typtype;
 			TupleDesc	tupdesc = RelationGetDescr(heap);
 			Form_pg_attribute att = TupleDescAttr(tupdesc, attno - 1);
-			TypeCacheEntry *typcache = lookup_type_cache(att->atttypid, 0);
+			TypeCacheEntry *typcache = lookup_type_cache(att->atttypid, TYPECACHE_DOMAIN_BASE_INFO);
+
+			if (OidIsValid(typcache->domainBaseType))
+				typtype = typcache->domainBaseTyptype;
+			else
+				typtype = typcache->typtype;
+
 
 			ExecWithoutOverlapsNotEmpty(heap, att->attname,
 										values[indnkeyatts - 1],
-										typcache->typtype, att->atttypid);
+										typtype, att->atttypid);
 		}
 	}
 
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index e96b38a59d5..67ec1aaad03 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -2792,6 +2792,8 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
 					if (!OidIsValid(typid) && column)
 						typid = typenameTypeId(NULL, column->typeName);
 
+					typid = getBaseType(typid);
+
 					if (!OidIsValid(typid) || !(type_is_range(typid) || type_is_multirange(typid)))
 						ereport(ERROR,
 								(errcode(ERRCODE_DATATYPE_MISMATCH),
diff --git a/src/backend/utils/cache/typcache.c b/src/backend/utils/cache/typcache.c
index 6a347698edf..e27ca1466f2 100644
--- a/src/backend/utils/cache/typcache.c
+++ b/src/backend/utils/cache/typcache.c
@@ -944,6 +944,8 @@ lookup_type_cache(Oid type_id, int flags)
 		typentry->domainBaseTypmod = -1;
 		typentry->domainBaseType =
 			getBaseTypeAndTypmod(type_id, &typentry->domainBaseTypmod);
+		typentry->domainBaseTyptype =
+			get_typtype(typentry->domainBaseType);
 	}
 	if ((flags & TYPECACHE_DOMAIN_CONSTR_INFO) &&
 		(typentry->flags & TCFLAGS_CHECKED_DOMAIN_CONSTRAINTS) == 0 &&
diff --git a/src/include/utils/typcache.h b/src/include/utils/typcache.h
index 1cb30f1818c..2ad7528c195 100644
--- a/src/include/utils/typcache.h
+++ b/src/include/utils/typcache.h
@@ -109,11 +109,12 @@ typedef struct TypeCacheEntry
 	struct TypeCacheEntry *rngtype; /* multirange's range underlying type */
 
 	/*
-	 * Domain's base type and typmod if it's a domain type.  Zeroes if not
-	 * domain, or if information hasn't been requested.
+	 * Domain's base type, typmod, and typtype if it's a domain type.
+	 * Zeroes if not domain, or if information hasn't been requested.
 	 */
 	Oid			domainBaseType;
 	int32		domainBaseTypmod;
+	char		domainBaseTyptype;
 
 	/*
 	 * Domain constraint data if it's a domain type.  NULL if not domain, or
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index f3144bdc39c..e3e5b8a4e8d 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -162,7 +162,48 @@ CREATE TABLE temporal_rng3 (
 );
 ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_pk;
 DROP TABLE temporal_rng3;
-DROP TYPE textrange2;
+CREATE DOMAIN d_textrange1 AS int4range CHECK (VALUE <> '[10,10]');
+CREATE DOMAIN d_textrange2 AS textrange2 CHECK (VALUE <> '[c,c]');
+CREATE DOMAIN d_textrange2c AS d_textrange2;
+CREATE TABLE temporal_rng4 (
+  id d_textrange1,
+  valid_at d_textrange2c,
+  CONSTRAINT temporal_rng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[c,c]'); --domain constraint violation
+ERROR:  value for domain d_textrange2c violates check constraint "d_textrange2_check"
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[a,f]');
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[b,b]'); --error
+ERROR:  conflicting key value violates exclusion constraint "temporal_rng4_pk"
+DETAIL:  Key (id, valid_at)=([1,2), [b,b]) conflicts with existing key (id, valid_at)=([1,2), [a,f]).
+INSERT INTO temporal_rng4 VALUES ('[2,2]', '[B,C)'), ('[2,2]', '[A,C)'); --error
+ERROR:  conflicting key value violates exclusion constraint "temporal_rng4_pk"
+DETAIL:  Key (id, valid_at)=([2,3), [A,C)) conflicts with existing key (id, valid_at)=([2,3), [B,C)).
+CREATE TABLE temporal_rngfk (parent_id d_textrange1, id int4range, valid_at d_textrange2);
+ALTER TABLE temporal_rngfk
+  ADD CONSTRAINT temporal_rngfk_fk
+    FOREIGN KEY (parent_id, PERIOD valid_at)
+    REFERENCES temporal_rng4;
+INSERT INTO temporal_rngfk VALUES ('[1,1]', '[2,2]', '[d,d]');
+TABLE temporal_rng4;
+  id   | valid_at 
+-------+----------
+ [1,2) | [a,f]
+(1 row)
+
+TABLE temporal_rngfk;
+ parent_id |  id   | valid_at 
+-----------+-------+----------
+ [1,2)     | [2,3) | [d,d]
+(1 row)
+
+UPDATE temporal_rng4 SET valid_at = '[c,c]'; --error
+ERROR:  value for domain d_textrange2c violates check constraint "d_textrange2_check"
+UPDATE temporal_rng4 SET valid_at = '[a,g]';
+UPDATE temporal_rng4 SET valid_at = '[f,f]'; --error
+ERROR:  update or delete on table "temporal_rng4" violates foreign key constraint "temporal_rngfk_fk" on table "temporal_rngfk"
+DETAIL:  Key (id, valid_at)=([1,2), [a,g]) is still referenced from table "temporal_rngfk".
+DROP TABLE temporal_rng4, temporal_rngfk;
 -- PK with one column plus a multirange:
 CREATE TABLE temporal_mltrng (
   id int4range,
@@ -301,7 +342,6 @@ SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'te
 
 DROP TABLE temporal_rng3;
 -- UNIQUE with a custom range type:
-CREATE TYPE textrange2 AS range (subtype=text, collation="C");
 CREATE TABLE temporal_rng3 (
   id int4range,
   valid_at textrange2,
@@ -309,6 +349,25 @@ CREATE TABLE temporal_rng3 (
 );
 ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_uq;
 DROP TABLE temporal_rng3;
+CREATE TABLE temporal_rng4 (
+  id d_textrange1,
+  valid_at d_textrange2c,
+  CONSTRAINT temporal_rng4_pk UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4 VALUES ('[1,1]', NULL), (NULL, '[1,1]');
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[c,c]'); --domain constraint violation
+ERROR:  value for domain d_textrange2c violates check constraint "d_textrange2_check"
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[a,c]');
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[b,b]'); --error
+ERROR:  conflicting key value violates exclusion constraint "temporal_rng4_pk"
+DETAIL:  Key (id, valid_at)=([1,2), [b,b]) conflicts with existing key (id, valid_at)=([1,2), [a,c]).
+INSERT INTO temporal_rng4 VALUES ('[10,10]', NULL); --error
+ERROR:  value for domain d_textrange1 violates check constraint "d_textrange1_check"
+INSERT INTO temporal_rng4 VALUES ('[2,2]', '[B,C)'), ('[2,2]', '[A,C)'); --error
+ERROR:  conflicting key value violates exclusion constraint "temporal_rng4_pk"
+DETAIL:  Key (id, valid_at)=([2,3), [A,C)) conflicts with existing key (id, valid_at)=([2,3), [B,C)).
+DROP TABLE temporal_rng4;
+DROP DOMAIN d_textrange1, d_textrange2c, d_textrange2;
 DROP TYPE textrange2;
 --
 -- test ALTER TABLE ADD CONSTRAINT
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index 4aaca242bbe..a984dd0f372 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -100,7 +100,36 @@ CREATE TABLE temporal_rng3 (
 );
 ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_pk;
 DROP TABLE temporal_rng3;
-DROP TYPE textrange2;
+
+CREATE DOMAIN d_textrange1 AS int4range CHECK (VALUE <> '[10,10]');
+CREATE DOMAIN d_textrange2 AS textrange2 CHECK (VALUE <> '[c,c]');
+CREATE DOMAIN d_textrange2c AS d_textrange2;
+CREATE TABLE temporal_rng4 (
+  id d_textrange1,
+  valid_at d_textrange2c,
+  CONSTRAINT temporal_rng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[c,c]'); --domain constraint violation
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[a,f]');
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[b,b]'); --error
+INSERT INTO temporal_rng4 VALUES ('[2,2]', '[B,C)'), ('[2,2]', '[A,C)'); --error
+
+CREATE TABLE temporal_rngfk (parent_id d_textrange1, id int4range, valid_at d_textrange2);
+ALTER TABLE temporal_rngfk
+  ADD CONSTRAINT temporal_rngfk_fk
+    FOREIGN KEY (parent_id, PERIOD valid_at)
+    REFERENCES temporal_rng4;
+
+INSERT INTO temporal_rngfk VALUES ('[1,1]', '[2,2]', '[d,d]');
+TABLE temporal_rng4;
+TABLE temporal_rngfk;
+
+UPDATE temporal_rng4 SET valid_at = '[c,c]'; --error
+UPDATE temporal_rng4 SET valid_at = '[a,g]';
+UPDATE temporal_rng4 SET valid_at = '[f,f]'; --error
+
+DROP TABLE temporal_rng4, temporal_rngfk;
 
 -- PK with one column plus a multirange:
 CREATE TABLE temporal_mltrng (
@@ -170,7 +199,6 @@ SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'te
 DROP TABLE temporal_rng3;
 
 -- UNIQUE with a custom range type:
-CREATE TYPE textrange2 AS range (subtype=text, collation="C");
 CREATE TABLE temporal_rng3 (
   id int4range,
   valid_at textrange2,
@@ -178,6 +206,21 @@ CREATE TABLE temporal_rng3 (
 );
 ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_uq;
 DROP TABLE temporal_rng3;
+
+CREATE TABLE temporal_rng4 (
+  id d_textrange1,
+  valid_at d_textrange2c,
+  CONSTRAINT temporal_rng4_pk UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4 VALUES ('[1,1]', NULL), (NULL, '[1,1]');
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[c,c]'); --domain constraint violation
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[a,c]');
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[b,b]'); --error
+INSERT INTO temporal_rng4 VALUES ('[10,10]', NULL); --error
+INSERT INTO temporal_rng4 VALUES ('[2,2]', '[B,C)'), ('[2,2]', '[A,C)'); --error
+
+DROP TABLE temporal_rng4;
+DROP DOMAIN d_textrange1, d_textrange2c, d_textrange2;
 DROP TYPE textrange2;
 
 --
-- 
2.34.1



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: domain for WITHOUT OVERLAPS
  2025-12-03 07:38 domain for WITHOUT OVERLAPS jian he <[email protected]>
@ 2025-12-03 18:39 ` Paul A Jungwirth <[email protected]>
  2025-12-11 04:23   ` Re: domain for WITHOUT OVERLAPS jian he <[email protected]>
  0 siblings, 1 reply; 11+ messages in thread

From: Paul A Jungwirth @ 2025-12-03 18:39 UTC (permalink / raw)
  To: jian he <[email protected]>; +Cc: pgsql-hackers

On Tue, Dec 2, 2025 at 11:39 PM jian he <[email protected]> wrote:
>
> While working on domain IS JSON, I found out that
> WITHOUT OVERLAPS does not support for domain too.
> but it does support user-defined range types (via CREATE TYPE).
>
> after looking around:
> check_exclusion_or_unique_constraint->ExecWithoutOverlapsNotEmpty
> ExecWithoutOverlapsNotEmpty typtype should be domain's basetype's typtype
> otherwise it will fallback to:
>             elog(ERROR, "WITHOUT OVERLAPS column \"%s\" is not a range
> or multirange",
>                  NameStr(attname));
>
> That means we need to cheaply get the domain basetype's
> pg_type.typtype in lookup_type_cache.
> so I added a new char field: TypeCacheEntry.domainBaseTyptype.

Thanks for the bug report and fix! Have you created a commitfest entry
for this? I didn't find one.

+CREATE DOMAIN d_textrange1 AS int4range CHECK (VALUE <> '[10,10]');
+CREATE DOMAIN d_textrange2 AS textrange2 CHECK (VALUE <> '[c,c]');
+CREATE DOMAIN d_textrange2c AS d_textrange2;

The first domain should be called d_int4range or d_int4range1, right?

Also let's name them like int4range_d so that we can use int4_d_range
for a range over a domain.

Please use closed/open notation to match the rest of the ranges in the
file: '[10,11)' and '[c,d)'.

I think there are these cases to consider:

- WITHOUT OVERLAPS on a rangetype whose subtype has a domain
- WITHOUT OVERLAPS on a rangetype with a domain on itself
- WITHOUT OVERLAPS on a multirangetype whose subtype has a domain
- WITHOUT OVERLAPS on a multirangetype whose rangetype has a domain
- WITHOUT OVERLAPS on a multirangetype with a domain on itself

For instance we could set things up like so:

-- range+multirange over a domain:
create domain int4_d as integer check (value <> 10);
create type int4_d_range as range (subtype = int4_d);

-- domain on a range:
create domain int4range_d as int4range check (value <> '[10,11)');

-- domain on a multirange:
create domain int4multirange_d as int4multirange check (value <> '{[10,11)}');

Then we have this:

```
[v19devel:5432][426675] postgres=# select oid, typname, typtype,
typbasetype, typtypmod from pg_type where oid >= 20000 order by oid ;
  oid  |      typname       | typtype | typbasetype | typtypmod
-------+--------------------+---------+-------------+-----------
 24595 | _int4_d            | b       |           0 |        -1
 24596 | int4_d             | d       |          23 |        -1
 24598 | _int4_d_range      | b       |           0 |        -1
 24599 | int4_d_multirange  | m       |           0 |        -1
 24600 | _int4_d_multirange | b       |           0 |        -1
 24601 | int4_d_range       | r       |           0 |        -1
 24608 | _int4range_d       | b       |           0 |        -1
 24609 | int4range_d        | d       |        3904 |        -1
 24611 | _int4multirange_d  | b       |           0 |        -1
 24612 | int4multirange_d   | d       |        4451 |        -1
```

Note that creating a domain on a range does not create a corresponding
multirange. Maybe that is a bug. It means we can't test the 4th case
above.

It looks like domains on the subtype work, but not directly on a range
or multirange:

```
[v19devel:5432][426675] postgres=# create table t1 (id int4range,
valid_at int4_d_range, primary key (id, valid_at without overlaps));
CREATE TABLE
[v19devel:5432][426675] postgres=# create table t2 (id int4range,
valid_at int4range_d, primary key (id, valid_at without overlaps));
ERROR:  column "valid_at" in WITHOUT OVERLAPS is not a range or multirange type
LINE 1: ...ate table t2 (id int4range, valid_at int4range_d, primary ke...
                                                             ^
[v19devel:5432][426675] postgres=# create table t3 (id int4range,
valid_at int4_d_multirange, primary key (id, valid_at without
overlaps));
CREATE TABLE
[v19devel:5432][426675] postgres=# create table t4 (id int4range,
valid_at int4_multirange_d, primary key (id, valid_at without
overlaps));
ERROR:  type "int4_multirange_d" does not exist
LINE 1: create table t4 (id int4range, valid_at int4_multirange_d, p...
```

It would be good to have tests for all of those. For instance:

```
-- domain on a multirange:

CREATE DOMAIN textmultirange2_d AS textmultirange2 CHECK (VALUE <> '{[c,d)}');
CREATE TABLE temporal_mltrng4 (
  id d_int4range1,
  valid_at textmultirange2_d,
  CONSTRAINT temporal_rng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
);

INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[c,d)}'); --domain
constraint violation
INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[a,g)}');
INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[b,c)}'); --error
INSERT INTO temporal_mltrng4 VALUES ('[2,3)', '{[B,C)}'), ('[2,2]',
'{[A,C)}'); --error

CREATE TABLE temporal_mltrngfk (parent_id d_int4range1, id int4range,
valid_at textmultirange2_d);
ALTER TABLE temporal_mltrngfk
  ADD CONSTRAINT temporal_mltrngfk_fk
    FOREIGN KEY (parent_id, PERIOD valid_at)
    REFERENCES temporal_mltrng4;

INSERT INTO temporal_mltrngfk VALUES ('[1,2)', '[2,3)', '{[d,e)}');
TABLE temporal_mltrng4;
TABLE temporal_mltrngfk;

UPDATE temporal_mltrng4 SET valid_at = '{[c,d)}'; --error
UPDATE temporal_mltrng4 SET valid_at = '{[a,h)}';
UPDATE temporal_mltrng4 SET valid_at = '{[f,g)}'; --error

DROP TABLE temporal_mltrng4, temporal_mltrngfk;
```

When I try that, it looks like your patch fixes multiranges too.

Yours,

-- 
Paul              ~{:-)
[email protected]





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: domain for WITHOUT OVERLAPS
  2025-12-03 07:38 domain for WITHOUT OVERLAPS jian he <[email protected]>
  2025-12-03 18:39 ` Re: domain for WITHOUT OVERLAPS Paul A Jungwirth <[email protected]>
@ 2025-12-11 04:23   ` jian he <[email protected]>
  2025-12-23 03:25     ` Re: domain for WITHOUT OVERLAPS Paul A Jungwirth <[email protected]>
  0 siblings, 1 reply; 11+ messages in thread

From: jian he @ 2025-12-11 04:23 UTC (permalink / raw)
  To: Paul A Jungwirth <[email protected]>; +Cc: pgsql-hackers

On Thu, Dec 4, 2025 at 2:39 AM Paul A Jungwirth
<[email protected]> wrote:
>
> I think there are these cases to consider:
>
> - WITHOUT OVERLAPS on a rangetype whose subtype has a domain
> - WITHOUT OVERLAPS on a rangetype with a domain on itself
> - WITHOUT OVERLAPS on a multirangetype whose subtype has a domain
> - WITHOUT OVERLAPS on a multirangetype whose rangetype has a domain
> - WITHOUT OVERLAPS on a multirangetype with a domain on itself
>
hi.

please check the attached polished test. (no C code change).

I think the test I am missing is
> - WITHOUT OVERLAPS on a multirangetype whose rangetype has a domain
because it expects CREATE DOMAIN also to create a multirange type for rangetype.
but currently we didn't do it.


--
jian
https://www.enterprisedb.com


Attachments:

  [text/x-patch] v2-0001-domain-for-WITHOUT-OVERLAPS.patch (16.8K, 2-v2-0001-domain-for-WITHOUT-OVERLAPS.patch)
  download | inline diff:
From eda1820f0aa597d5c28c89e870aa1384556f6224 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Thu, 11 Dec 2025 12:20:38 +0800
Subject: [PATCH v2 1/1] domain for WITHOUT OVERLAPS

polished tests for
> - WITHOUT OVERLAPS on a rangetype whose subtype has a domain
> - WITHOUT OVERLAPS on a rangetype with a domain on itself
> - WITHOUT OVERLAPS on a multirangetype whose subtype has a domain
> - WITHOUT OVERLAPS on a multirangetype whose rangetype has a domain
> - WITHOUT OVERLAPS on a multirangetype with a domain on itself

no tests for
> - WITHOUT OVERLAPS on a multirangetype whose rangetype has a domain
now.

because the expected behavior assumes that CREATE DOMAIN also generates a
corresponding multirange type for the domain range type, which is not true.

--example, we expect create domain also create a multirange type for xxint4
CREATE DOMAIN xxint4 AS int4range CHECK (VALUE <> '[10,11)');
SELECT typname FROM pg_type WHERE typname ~* 'xxint4';

commitfest: https://commitfest.postgresql.org/patch/6281
discussion: https://postgr.es/m/CACJufxGoAmN_0iJ=hjTG0vGpOSOyy-vYyfE+-q0AWxrq2_p5XQ@mail.gmail.com
---
 src/backend/executor/execIndexing.c           |  10 +-
 src/backend/parser/parse_utilcmd.c            |   2 +
 src/backend/utils/cache/typcache.c            |   2 +
 src/include/utils/typcache.h                  |   5 +-
 .../regress/expected/without_overlaps.out     | 135 +++++++++++++++++-
 src/test/regress/sql/without_overlaps.sql     | 104 +++++++++++++-
 6 files changed, 250 insertions(+), 8 deletions(-)

diff --git a/src/backend/executor/execIndexing.c b/src/backend/executor/execIndexing.c
index dd323c9b9fd..6a46ef21113 100644
--- a/src/backend/executor/execIndexing.c
+++ b/src/backend/executor/execIndexing.c
@@ -752,13 +752,19 @@ check_exclusion_or_unique_constraint(Relation heap, Relation index,
 
 		if (!isnull[indnkeyatts - 1])
 		{
+			char 		typtype;
 			TupleDesc	tupdesc = RelationGetDescr(heap);
 			Form_pg_attribute att = TupleDescAttr(tupdesc, attno - 1);
-			TypeCacheEntry *typcache = lookup_type_cache(att->atttypid, 0);
+			TypeCacheEntry *typcache = lookup_type_cache(att->atttypid, TYPECACHE_DOMAIN_BASE_INFO);
+
+			if (OidIsValid(typcache->domainBaseType))
+				typtype = typcache->domainBaseTyptype;
+			else
+				typtype = typcache->typtype;
 
 			ExecWithoutOverlapsNotEmpty(heap, att->attname,
 										values[indnkeyatts - 1],
-										typcache->typtype, att->atttypid);
+										typtype, att->atttypid);
 		}
 	}
 
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index e96b38a59d5..67ec1aaad03 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -2792,6 +2792,8 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
 					if (!OidIsValid(typid) && column)
 						typid = typenameTypeId(NULL, column->typeName);
 
+					typid = getBaseType(typid);
+
 					if (!OidIsValid(typid) || !(type_is_range(typid) || type_is_multirange(typid)))
 						ereport(ERROR,
 								(errcode(ERRCODE_DATATYPE_MISMATCH),
diff --git a/src/backend/utils/cache/typcache.c b/src/backend/utils/cache/typcache.c
index 6a347698edf..e27ca1466f2 100644
--- a/src/backend/utils/cache/typcache.c
+++ b/src/backend/utils/cache/typcache.c
@@ -944,6 +944,8 @@ lookup_type_cache(Oid type_id, int flags)
 		typentry->domainBaseTypmod = -1;
 		typentry->domainBaseType =
 			getBaseTypeAndTypmod(type_id, &typentry->domainBaseTypmod);
+		typentry->domainBaseTyptype =
+			get_typtype(typentry->domainBaseType);
 	}
 	if ((flags & TYPECACHE_DOMAIN_CONSTR_INFO) &&
 		(typentry->flags & TCFLAGS_CHECKED_DOMAIN_CONSTRAINTS) == 0 &&
diff --git a/src/include/utils/typcache.h b/src/include/utils/typcache.h
index 1cb30f1818c..2ad7528c195 100644
--- a/src/include/utils/typcache.h
+++ b/src/include/utils/typcache.h
@@ -109,11 +109,12 @@ typedef struct TypeCacheEntry
 	struct TypeCacheEntry *rngtype; /* multirange's range underlying type */
 
 	/*
-	 * Domain's base type and typmod if it's a domain type.  Zeroes if not
-	 * domain, or if information hasn't been requested.
+	 * Domain's base type, typmod, and typtype if it's a domain type.
+	 * Zeroes if not domain, or if information hasn't been requested.
 	 */
 	Oid			domainBaseType;
 	int32		domainBaseTypmod;
+	char		domainBaseTyptype;
 
 	/*
 	 * Domain constraint data if it's a domain type.  NULL if not domain, or
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index f3144bdc39c..1ee88c4230f 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -162,7 +162,118 @@ CREATE TABLE temporal_rng3 (
 );
 ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_pk;
 DROP TABLE temporal_rng3;
-DROP TYPE textrange2;
+--
+-- tests for range over domain, multirange over a domain, custom range type over
+-- domain.
+--
+CREATE DOMAIN int4_d as integer check (value <> 10);
+CREATE TYPE int4_d_range as range (subtype = int4_d);
+CREATE DOMAIN int4multirange_d as int4multirange check (value <> '{[10,11)}');
+CREATE DOMAIN d_int4range1 AS int4range CHECK (VALUE <> '[10,11)');
+CREATE DOMAIN d_textrange2 AS textrange2 CHECK (VALUE <> '[c,d)');
+CREATE DOMAIN d_textrange2c AS d_textrange2;
+CREATE DOMAIN textmultirange2_d AS textmultirange2 CHECK (VALUE <> '{[c,d)}');
+CREATE TABLE temporal_rng4mrng (
+  id  d_int4range1,
+  valid_at int4multirange_d,
+  CONSTRAINT temporal_rng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4mrng VALUES ('[1,2)', '{[10,11)}'); --error
+ERROR:  value for domain int4multirange_d violates check constraint "int4multirange_d_check"
+INSERT INTO temporal_rng4mrng VALUES ('[1,2)', '{[10,13)}'), ('[1,2)', '{[2,13)}'); --error
+ERROR:  conflicting key value violates exclusion constraint "temporal_rng4_pk"
+DETAIL:  Key (id, valid_at)=([1,2), {[2,13)}) conflicts with existing key (id, valid_at)=([1,2), {[10,13)}).
+DROP TABLE temporal_rng4mrng;
+CREATE TABLE temporal_rng4 (
+  id  d_int4range1,
+  id1 int4_d_range,
+  valid_at d_textrange2c,
+  CONSTRAINT temporal_rng4_pk PRIMARY KEY (id, id1, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[1,2)', '[c,d)'); --error
+ERROR:  value for domain d_textrange2c violates check constraint "d_textrange2_check"
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[10,12)', '[a,g)'); --error
+ERROR:  value for domain int4_d violates check constraint "int4_d_check"
+LINE 1: INSERT INTO temporal_rng4 VALUES ('[1,2)', '[10,12)', '[a,g)...
+                                                   ^
+INSERT INTO temporal_rng4
+  VALUES ('[2,3)', '[9,11)', '[B,C)'), ('[2,3)', '[9,11)', '[A,C)'); --error
+ERROR:  conflicting key value violates exclusion constraint "temporal_rng4_pk"
+DETAIL:  Key (id, id1, valid_at)=([2,3), [9,11), [A,C)) conflicts with existing key (id, id1, valid_at)=([2,3), [9,11), [B,C)).
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[9,11)', '[a,g)');
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[9,11)', '[b,c)'); --error
+ERROR:  conflicting key value violates exclusion constraint "temporal_rng4_pk"
+DETAIL:  Key (id, id1, valid_at)=([1,2), [9,11), [b,c)) conflicts with existing key (id, id1, valid_at)=([1,2), [9,11), [a,g)).
+CREATE TABLE temporal_rngfk (
+  parent_id d_int4range1,
+  id int4range,
+  id1 int4_d_range,
+  valid_at d_textrange2);
+ALTER TABLE temporal_rngfk
+  ADD CONSTRAINT temporal_rngfk_fk
+    FOREIGN KEY (parent_id, id1, PERIOD valid_at)
+    REFERENCES temporal_rng4;
+INSERT INTO temporal_rngfk VALUES ('[1,2)', '[2,3)', '[9,11)', '[d,e)');
+TABLE temporal_rng4;
+  id   |  id1   | valid_at 
+-------+--------+----------
+ [1,2) | [9,11) | [a,g)
+(1 row)
+
+TABLE temporal_rngfk;
+ parent_id |  id   |  id1   | valid_at 
+-----------+-------+--------+----------
+ [1,2)     | [2,3) | [9,11) | [d,e)
+(1 row)
+
+UPDATE temporal_rng4 SET valid_at = '[c,d)'; --error
+ERROR:  value for domain d_textrange2c violates check constraint "d_textrange2_check"
+UPDATE temporal_rng4 SET valid_at = '[a,h)';
+UPDATE temporal_rng4 SET valid_at = '[f,g)'; --error
+ERROR:  update or delete on table "temporal_rng4" violates foreign key constraint "temporal_rngfk_fk" on table "temporal_rngfk"
+DETAIL:  Key (id, id1, valid_at)=([1,2), [9,11), [a,h)) is still referenced from table "temporal_rngfk".
+DROP TABLE temporal_rng4, temporal_rngfk;
+-- domain on a multirange
+CREATE TABLE temporal_mltrng4 (
+  id d_int4range1,
+  valid_at textmultirange2_d,
+  CONSTRAINT temporal_rng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[c,d)}'); --error
+ERROR:  value for domain textmultirange2_d violates check constraint "textmultirange2_d_check"
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[a,g)}');
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[b,c)}'); --error
+ERROR:  conflicting key value violates exclusion constraint "temporal_rng4_pk"
+DETAIL:  Key (id, valid_at)=([1,2), {[b,c)}) conflicts with existing key (id, valid_at)=([1,2), {[a,g)}).
+INSERT INTO temporal_mltrng4 VALUES ('[2,3)', '{[B,C)}'), ('[2,3)', '{[A,C)}'); --error
+ERROR:  conflicting key value violates exclusion constraint "temporal_rng4_pk"
+DETAIL:  Key (id, valid_at)=([2,3), {[A,C)}) conflicts with existing key (id, valid_at)=([2,3), {[B,C)}).
+CREATE TABLE temporal_mltrngfk (parent_id d_int4range1, id int4range,
+valid_at textmultirange2_d);
+ALTER TABLE temporal_mltrngfk
+  ADD CONSTRAINT temporal_mltrngfk_fk
+    FOREIGN KEY (parent_id, PERIOD valid_at)
+    REFERENCES temporal_mltrng4;
+INSERT INTO temporal_mltrngfk VALUES ('[1,2)', '[2,3)', '{[d,e)}');
+TABLE temporal_mltrng4;
+  id   | valid_at 
+-------+----------
+ [1,2) | {[a,g)}
+(1 row)
+
+TABLE temporal_mltrngfk;
+ parent_id |  id   | valid_at 
+-----------+-------+----------
+ [1,2)     | [2,3) | {[d,e)}
+(1 row)
+
+UPDATE temporal_mltrng4 SET valid_at = '{[c,d)}'; --error
+ERROR:  value for domain textmultirange2_d violates check constraint "textmultirange2_d_check"
+UPDATE temporal_mltrng4 SET valid_at = '{[a,h)}';
+UPDATE temporal_mltrng4 SET valid_at = '{[f,g)}'; --error
+ERROR:  update or delete on table "temporal_mltrng4" violates foreign key constraint "temporal_mltrngfk_fk" on table "temporal_mltrngfk"
+DETAIL:  Key (id, valid_at)=([1,2), {[a,h)}) is still referenced from table "temporal_mltrngfk".
+DROP TABLE temporal_mltrng4, temporal_mltrngfk;
 -- PK with one column plus a multirange:
 CREATE TABLE temporal_mltrng (
   id int4range,
@@ -301,7 +412,6 @@ SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'te
 
 DROP TABLE temporal_rng3;
 -- UNIQUE with a custom range type:
-CREATE TYPE textrange2 AS range (subtype=text, collation="C");
 CREATE TABLE temporal_rng3 (
   id int4range,
   valid_at textrange2,
@@ -309,6 +419,27 @@ CREATE TABLE temporal_rng3 (
 );
 ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_uq;
 DROP TABLE temporal_rng3;
+CREATE TABLE temporal_rng4 (
+  id d_int4range1,
+  valid_at d_textrange2c,
+  CONSTRAINT temporal_rng4_pk UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4 VALUES ('[1,2)', NULL), (NULL, '[1,2)');
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[c,d)'); --error
+ERROR:  value for domain d_textrange2c violates check constraint "d_textrange2_check"
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[a,d)');
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[b,c)'); --error
+ERROR:  conflicting key value violates exclusion constraint "temporal_rng4_pk"
+DETAIL:  Key (id, valid_at)=([1,2), [b,c)) conflicts with existing key (id, valid_at)=([1,2), [a,d)).
+INSERT INTO temporal_rng4 VALUES ('[10,11)', NULL); --error
+ERROR:  value for domain d_int4range1 violates check constraint "d_int4range1_check"
+INSERT INTO temporal_rng4 VALUES ('[2,3)', '[B,C)'), ('[2,3)', '[A,C)'); --error
+ERROR:  conflicting key value violates exclusion constraint "temporal_rng4_pk"
+DETAIL:  Key (id, valid_at)=([2,3), [A,C)) conflicts with existing key (id, valid_at)=([2,3), [B,C)).
+DROP TABLE temporal_rng4;
+DROP TYPE int4_d_range;
+DROP DOMAIN d_int4range1, d_textrange2c, d_textrange2,
+            textmultirange2_d, int4multirange_d, int4_d;
 DROP TYPE textrange2;
 --
 -- test ALTER TABLE ADD CONSTRAINT
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index 4aaca242bbe..4e0d2df7775 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -100,7 +100,91 @@ CREATE TABLE temporal_rng3 (
 );
 ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_pk;
 DROP TABLE temporal_rng3;
-DROP TYPE textrange2;
+
+--
+-- tests for range over domain, multirange over a domain, custom range type over
+-- domain.
+--
+CREATE DOMAIN int4_d as integer check (value <> 10);
+CREATE TYPE int4_d_range as range (subtype = int4_d);
+CREATE DOMAIN int4multirange_d as int4multirange check (value <> '{[10,11)}');
+CREATE DOMAIN d_int4range1 AS int4range CHECK (VALUE <> '[10,11)');
+CREATE DOMAIN d_textrange2 AS textrange2 CHECK (VALUE <> '[c,d)');
+CREATE DOMAIN d_textrange2c AS d_textrange2;
+CREATE DOMAIN textmultirange2_d AS textmultirange2 CHECK (VALUE <> '{[c,d)}');
+
+CREATE TABLE temporal_rng4mrng (
+  id  d_int4range1,
+  valid_at int4multirange_d,
+  CONSTRAINT temporal_rng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4mrng VALUES ('[1,2)', '{[10,11)}'); --error
+INSERT INTO temporal_rng4mrng VALUES ('[1,2)', '{[10,13)}'), ('[1,2)', '{[2,13)}'); --error
+DROP TABLE temporal_rng4mrng;
+
+CREATE TABLE temporal_rng4 (
+  id  d_int4range1,
+  id1 int4_d_range,
+  valid_at d_textrange2c,
+  CONSTRAINT temporal_rng4_pk PRIMARY KEY (id, id1, valid_at WITHOUT OVERLAPS)
+);
+
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[1,2)', '[c,d)'); --error
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[10,12)', '[a,g)'); --error
+INSERT INTO temporal_rng4
+  VALUES ('[2,3)', '[9,11)', '[B,C)'), ('[2,3)', '[9,11)', '[A,C)'); --error
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[9,11)', '[a,g)');
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[9,11)', '[b,c)'); --error
+
+CREATE TABLE temporal_rngfk (
+  parent_id d_int4range1,
+  id int4range,
+  id1 int4_d_range,
+  valid_at d_textrange2);
+ALTER TABLE temporal_rngfk
+  ADD CONSTRAINT temporal_rngfk_fk
+    FOREIGN KEY (parent_id, id1, PERIOD valid_at)
+    REFERENCES temporal_rng4;
+
+INSERT INTO temporal_rngfk VALUES ('[1,2)', '[2,3)', '[9,11)', '[d,e)');
+TABLE temporal_rng4;
+TABLE temporal_rngfk;
+
+UPDATE temporal_rng4 SET valid_at = '[c,d)'; --error
+UPDATE temporal_rng4 SET valid_at = '[a,h)';
+UPDATE temporal_rng4 SET valid_at = '[f,g)'; --error
+
+DROP TABLE temporal_rng4, temporal_rngfk;
+
+-- domain on a multirange
+CREATE TABLE temporal_mltrng4 (
+  id d_int4range1,
+  valid_at textmultirange2_d,
+  CONSTRAINT temporal_rng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[c,d)}'); --error
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[a,g)}');
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[b,c)}'); --error
+INSERT INTO temporal_mltrng4 VALUES ('[2,3)', '{[B,C)}'), ('[2,3)', '{[A,C)}'); --error
+
+CREATE TABLE temporal_mltrngfk (parent_id d_int4range1, id int4range,
+valid_at textmultirange2_d);
+ALTER TABLE temporal_mltrngfk
+  ADD CONSTRAINT temporal_mltrngfk_fk
+    FOREIGN KEY (parent_id, PERIOD valid_at)
+    REFERENCES temporal_mltrng4;
+
+INSERT INTO temporal_mltrngfk VALUES ('[1,2)', '[2,3)', '{[d,e)}');
+TABLE temporal_mltrng4;
+TABLE temporal_mltrngfk;
+
+UPDATE temporal_mltrng4 SET valid_at = '{[c,d)}'; --error
+UPDATE temporal_mltrng4 SET valid_at = '{[a,h)}';
+UPDATE temporal_mltrng4 SET valid_at = '{[f,g)}'; --error
+
+DROP TABLE temporal_mltrng4, temporal_mltrngfk;
+
 
 -- PK with one column plus a multirange:
 CREATE TABLE temporal_mltrng (
@@ -170,7 +254,6 @@ SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'te
 DROP TABLE temporal_rng3;
 
 -- UNIQUE with a custom range type:
-CREATE TYPE textrange2 AS range (subtype=text, collation="C");
 CREATE TABLE temporal_rng3 (
   id int4range,
   valid_at textrange2,
@@ -178,6 +261,23 @@ CREATE TABLE temporal_rng3 (
 );
 ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_uq;
 DROP TABLE temporal_rng3;
+
+CREATE TABLE temporal_rng4 (
+  id d_int4range1,
+  valid_at d_textrange2c,
+  CONSTRAINT temporal_rng4_pk UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4 VALUES ('[1,2)', NULL), (NULL, '[1,2)');
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[c,d)'); --error
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[a,d)');
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[b,c)'); --error
+INSERT INTO temporal_rng4 VALUES ('[10,11)', NULL); --error
+INSERT INTO temporal_rng4 VALUES ('[2,3)', '[B,C)'), ('[2,3)', '[A,C)'); --error
+
+DROP TABLE temporal_rng4;
+DROP TYPE int4_d_range;
+DROP DOMAIN d_int4range1, d_textrange2c, d_textrange2,
+            textmultirange2_d, int4multirange_d, int4_d;
 DROP TYPE textrange2;
 
 --
-- 
2.34.1



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: domain for WITHOUT OVERLAPS
  2025-12-03 07:38 domain for WITHOUT OVERLAPS jian he <[email protected]>
  2025-12-03 18:39 ` Re: domain for WITHOUT OVERLAPS Paul A Jungwirth <[email protected]>
  2025-12-11 04:23   ` Re: domain for WITHOUT OVERLAPS jian he <[email protected]>
@ 2025-12-23 03:25     ` Paul A Jungwirth <[email protected]>
  2025-12-23 18:08       ` Re: domain for WITHOUT OVERLAPS Paul A Jungwirth <[email protected]>
  2025-12-29 07:17       ` Re: domain for WITHOUT OVERLAPS jian he <[email protected]>
  0 siblings, 2 replies; 11+ messages in thread

From: Paul A Jungwirth @ 2025-12-23 03:25 UTC (permalink / raw)
  To: jian he <[email protected]>; +Cc: pgsql-hackers

On Wed, Dec 10, 2025 at 8:23 PM jian he <[email protected]> wrote:
> polished tests for
> > - WITHOUT OVERLAPS on a rangetype whose subtype has a domain
> > - WITHOUT OVERLAPS on a rangetype with a domain on itself
> > - WITHOUT OVERLAPS on a multirangetype whose subtype has a domain
> > - WITHOUT OVERLAPS on a multirangetype whose rangetype has a domain
> > - WITHOUT OVERLAPS on a multirangetype with a domain on itself
>
> no tests for
> > - WITHOUT OVERLAPS on a multirangetype whose rangetype has a domain
> now.
>
> because the expected behavior assumes that CREATE DOMAIN also generates a
> corresponding multirange type for the domain range type, which is not true.
>
> --example, we expect create domain also create a multirange type for xxint4
> CREATE DOMAIN xxint4 AS int4range CHECK (VALUE <> '[10,11)');
> SELECT typname FROM pg_type WHERE typname ~* 'xxint4';

I'm not sure whether creating a domain on a range should create a
multirange or not. I asked the list on another thread. Since it does
not create a new range constructor either (or an entry in pg_range),
maybe not creating a new multirange is correct. Anyway I think we can
fix the WITHOUT OVERLAPS issue without dealing with that.


> diff --git a/src/backend/utils/cache/typcache.c b/src/backend/utils/cache/typcache.c
> index 6a347698edf..e27ca1466f2 100644
> --- a/src/backend/utils/cache/typcache.c
> +++ b/src/backend/utils/cache/typcache.c
> @@ -944,6 +944,8 @@ lookup_type_cache(Oid type_id, int flags)
>          typentry->domainBaseTypmod = -1;
>          typentry->domainBaseType =
>              getBaseTypeAndTypmod(type_id, &typentry->domainBaseTypmod);
> +        typentry->domainBaseTyptype =
> +            get_typtype(typentry->domainBaseType);
>      }
>      if ((flags & TYPECACHE_DOMAIN_CONSTR_INFO) &&
>          (typentry->flags & TCFLAGS_CHECKED_DOMAIN_CONSTRAINTS) == 0 &&

Is there any performance concern about adding this lookup?


^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: domain for WITHOUT OVERLAPS
  2025-12-03 07:38 domain for WITHOUT OVERLAPS jian he <[email protected]>
  2025-12-03 18:39 ` Re: domain for WITHOUT OVERLAPS Paul A Jungwirth <[email protected]>
  2025-12-11 04:23   ` Re: domain for WITHOUT OVERLAPS jian he <[email protected]>
  2025-12-23 03:25     ` Re: domain for WITHOUT OVERLAPS Paul A Jungwirth <[email protected]>
@ 2025-12-23 18:08       ` Paul A Jungwirth <[email protected]>
  1 sibling, 0 replies; 11+ messages in thread

From: Paul A Jungwirth @ 2025-12-23 18:08 UTC (permalink / raw)
  To: jian he <[email protected]>; +Cc: pgsql-hackers

On Mon, Dec 22, 2025 at 7:25 PM Paul A Jungwirth
<[email protected]> wrote:
>
> On Wed, Dec 10, 2025 at 8:23 PM jian he <[email protected]> wrote:
> > +-- tests for range over domain, multirange over a domain, custom range type over
> > +-- domain.
> > +--
> > +CREATE DOMAIN int4_d as integer check (value <> 10);
> > +CREATE TYPE int4_d_range as range (subtype = int4_d);
> > +CREATE DOMAIN int4multirange_d as int4multirange check (value <> '{[10,11)}');
> > +CREATE DOMAIN d_int4range1 AS int4range CHECK (VALUE <> '[10,11)');
> > +CREATE DOMAIN d_textrange2 AS textrange2 CHECK (VALUE <> '[c,d)');
> > +CREATE DOMAIN d_textrange2c AS d_textrange2;
> > +CREATE DOMAIN textmultirange2_d AS textmultirange2 CHECK (VALUE <> '{[c,d)}');
>
> The comment doesn't seem to match. It mentions three scenarios, but I'm seeing:
>
> - int4_d_range is a range over a domain.
> - int4multirange_d is a domain over a multirange.
> - d_int4range1 is a domain over a range.
> - d_textrange2 is a domain over a custom range.
> - d_textrange2c is a domain over a domain, with no extra constraint.
> What is this one for?
> - textmultirange2_d is a domain over a custom multirange.

Another variation I'm not seeing tested yet is a multirange over a
domain. In other words what ranges call the "subtype" is a domain.
Such a multirange is already created by these lines:

> > +CREATE DOMAIN int4_d as integer check (value <> 10);
> > +CREATE TYPE int4_d_range as range (subtype = int4_d);

For example we can use it:

[v19devel:5432][454113] postgres=# select '{[1,2)}'::int4_d_multirange;
 int4_d_multirange
-------------------
 {[1,2)}
(1 row)

[v19devel:5432][454113] postgres=# select '{[10,12)}'::int4_d_multirange;
ERROR:  value for domain int4_d violates check constraint "int4_d_check"
LINE 1: select '{[10,12)}'::int4_d_multirange;
               ^

So it seems worthwhile to test WITHOUT OVERLAPS with that variation as well.

Yours,

-- 
Paul              ~{:-)
[email protected]





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: domain for WITHOUT OVERLAPS
  2025-12-03 07:38 domain for WITHOUT OVERLAPS jian he <[email protected]>
  2025-12-03 18:39 ` Re: domain for WITHOUT OVERLAPS Paul A Jungwirth <[email protected]>
  2025-12-11 04:23   ` Re: domain for WITHOUT OVERLAPS jian he <[email protected]>
  2025-12-23 03:25     ` Re: domain for WITHOUT OVERLAPS Paul A Jungwirth <[email protected]>
@ 2025-12-29 07:17       ` jian he <[email protected]>
  2026-03-10 07:41         ` Re: domain for WITHOUT OVERLAPS jian he <[email protected]>
  2026-03-10 09:53         ` Re: domain for WITHOUT OVERLAPS Kirill Reshke <[email protected]>
  1 sibling, 2 replies; 11+ messages in thread

From: jian he @ 2025-12-29 07:17 UTC (permalink / raw)
  To: Paul A Jungwirth <[email protected]>; +Cc: pgsql-hackers

On Tue, Dec 23, 2025 at 11:25 AM Paul A Jungwirth
<[email protected]> wrote:
>
> > diff --git a/src/backend/utils/cache/typcache.c b/src/backend/utils/cache/typcache.c
> > index 6a347698edf..e27ca1466f2 100644
> > --- a/src/backend/utils/cache/typcache.c
> > +++ b/src/backend/utils/cache/typcache.c
> > @@ -944,6 +944,8 @@ lookup_type_cache(Oid type_id, int flags)
> >          typentry->domainBaseTypmod = -1;
> >          typentry->domainBaseType =
> >              getBaseTypeAndTypmod(type_id, &typentry->domainBaseTypmod);
> > +        typentry->domainBaseTyptype =
> > +            get_typtype(typentry->domainBaseType);
> >      }
> >      if ((flags & TYPECACHE_DOMAIN_CONSTR_INFO) &&
> >          (typentry->flags & TCFLAGS_CHECKED_DOMAIN_CONSTRAINTS) == 0 &&
>
> Is there any performance concern about adding this lookup?
> From what I can tell we don't use TYPECACHE_DOMAIN_BASE_INFO very
> often, so it is probably okay.
> We can get here in the executor from ExecEvalWholeRowVar, but that
> seems acceptable to me.
>
>
hi.

To make WITHOUT OVERLAPS work with domains, ExecWithoutOverlapsNotEmpty requires
the base type’s typtype. I do not see a viable alternative approach.

We can cache the base type's typtype in TypeCacheEntry->domainBaseTyptype.

```
    if ((flags & TYPECACHE_DOMAIN_BASE_INFO) &&
        typentry->domainBaseType == InvalidOid &&
        typentry->typtype == TYPTYPE_DOMAIN)
    {
        typentry->domainBaseTypmod = -1;
        typentry->domainBaseType =
            getBaseTypeAndTypmod(type_id, &typentry->domainBaseTypmod);
        typentry->domainBaseTyptype =
            get_typtype(typentry->domainBaseType);
    }
```

We look up the domain base type’s typtype only once. On subsequent
lookup_type_cache call, typentry->domainBaseType already is a valid OID, so the
IF branch above will not be reached.
so. I don't see any performance issues here.

>
> Also testing a range over a domain (in WITHOUT OVERLAPS position) would be good.
>
I do not think we need extensive foreign key–related tests, because
check_exclusion_or_unique_constraint is only invoked for data changes on the
primary key side. So, I kept only a single foreign key–related test.

The attached patch should address all of your comments.
I think you will like attached regress tests.



--
jian
https://www.enterprisedb.com/


Attachments:

  [text/x-patch] v3-0001-Add-support-for-domain-types-in-WITHOUT-OVERLAPS.patch (17.1K, 2-v3-0001-Add-support-for-domain-types-in-WITHOUT-OVERLAPS.patch)
  download | inline diff:
From 041198f2fb6674060675980e4bfca6ff16074f13 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 29 Dec 2025 15:15:23 +0800
Subject: [PATCH v3 1/1] Add support for domain types in WITHOUT OVERLAPS

This commit allows domain types to work with the WITHOUT OVERLAPS constraint.
Domains over range types and domains over multirange types are both supported.
User-defined range types whose subtype is a domain are also supported.

commitfest: https://commitfest.postgresql.org/patch/6281
discussion: https://postgr.es/m/CACJufxGoAmN_0iJ=hjTG0vGpOSOyy-vYyfE+-q0AWxrq2_p5XQ@mail.gmail.com
---
 src/backend/executor/execIndexing.c           |  10 +-
 src/backend/parser/parse_utilcmd.c            |   2 +
 src/backend/utils/cache/typcache.c            |   2 +
 src/include/utils/typcache.h                  |   5 +-
 .../regress/expected/without_overlaps.out     | 128 +++++++++++++++++-
 src/test/regress/sql/without_overlaps.sql     | 104 +++++++++++++-
 6 files changed, 243 insertions(+), 8 deletions(-)

diff --git a/src/backend/executor/execIndexing.c b/src/backend/executor/execIndexing.c
index 0b3a31f1703..67eddcac0f3 100644
--- a/src/backend/executor/execIndexing.c
+++ b/src/backend/executor/execIndexing.c
@@ -752,13 +752,19 @@ check_exclusion_or_unique_constraint(Relation heap, Relation index,
 
 		if (!isnull[indnkeyatts - 1])
 		{
+			char 		typtype;
 			TupleDesc	tupdesc = RelationGetDescr(heap);
 			Form_pg_attribute att = TupleDescAttr(tupdesc, attno - 1);
-			TypeCacheEntry *typcache = lookup_type_cache(att->atttypid, 0);
+			TypeCacheEntry *typcache = lookup_type_cache(att->atttypid, TYPECACHE_DOMAIN_BASE_INFO);
+
+			if (OidIsValid(typcache->domainBaseType))
+				typtype = typcache->domainBaseTyptype;
+			else
+				typtype = typcache->typtype;
 
 			ExecWithoutOverlapsNotEmpty(heap, att->attname,
 										values[indnkeyatts - 1],
-										typcache->typtype, att->atttypid);
+										typtype, att->atttypid);
 		}
 	}
 
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 2b7b084f216..33360ba0761 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -2795,6 +2795,8 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
 					if (!OidIsValid(typid) && column)
 						typid = typenameTypeId(NULL, column->typeName);
 
+					typid = getBaseType(typid);
+
 					if (!OidIsValid(typid) || !(type_is_range(typid) || type_is_multirange(typid)))
 						ereport(ERROR,
 								(errcode(ERRCODE_DATATYPE_MISMATCH),
diff --git a/src/backend/utils/cache/typcache.c b/src/backend/utils/cache/typcache.c
index 0c17d99d021..839d1e3ebcf 100644
--- a/src/backend/utils/cache/typcache.c
+++ b/src/backend/utils/cache/typcache.c
@@ -944,6 +944,8 @@ lookup_type_cache(Oid type_id, int flags)
 		typentry->domainBaseTypmod = -1;
 		typentry->domainBaseType =
 			getBaseTypeAndTypmod(type_id, &typentry->domainBaseTypmod);
+		typentry->domainBaseTyptype =
+			get_typtype(typentry->domainBaseType);
 	}
 	if ((flags & TYPECACHE_DOMAIN_CONSTR_INFO) &&
 		(typentry->flags & TCFLAGS_CHECKED_DOMAIN_CONSTRAINTS) == 0 &&
diff --git a/src/include/utils/typcache.h b/src/include/utils/typcache.h
index 1cb30f1818c..2ad7528c195 100644
--- a/src/include/utils/typcache.h
+++ b/src/include/utils/typcache.h
@@ -109,11 +109,12 @@ typedef struct TypeCacheEntry
 	struct TypeCacheEntry *rngtype; /* multirange's range underlying type */
 
 	/*
-	 * Domain's base type and typmod if it's a domain type.  Zeroes if not
-	 * domain, or if information hasn't been requested.
+	 * Domain's base type, typmod, and typtype if it's a domain type.
+	 * Zeroes if not domain, or if information hasn't been requested.
 	 */
 	Oid			domainBaseType;
 	int32		domainBaseTypmod;
+	char		domainBaseTyptype;
 
 	/*
 	 * Domain constraint data if it's a domain type.  NULL if not domain, or
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index f3144bdc39c..4853302cae5 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -162,7 +162,129 @@ CREATE TABLE temporal_rng3 (
 );
 ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_pk;
 DROP TABLE temporal_rng3;
-DROP TYPE textrange2;
+--
+-- tests for range over domain, multirange over a domain, custom range type over
+-- domain, custom multi range type over domain.
+--
+CREATE DOMAIN int4_d as integer check (value <> 10);
+CREATE TYPE int4_d_range as range (subtype = int4_d);
+CREATE DOMAIN int4multirange_d as int4multirange check (value <> '{[10,11)}');
+CREATE DOMAIN int4range_d AS int4range CHECK (VALUE <> '[10,11)');
+CREATE DOMAIN textrange2_d AS textrange2 CHECK (VALUE <> '[c,d)');
+CREATE DOMAIN textrange2_dd AS textrange2_d;
+CREATE DOMAIN textmultirange2_d AS textmultirange2 CHECK (VALUE <> '{[c,d)}');
+-- It is a range type rather than a domain type; however, the range type’s
+-- subtype is a domain type.
+CREATE TABLE temporal_rng4 (
+  id int4range_d,
+  valid_at int4_d_range,
+  CONSTRAINT temporal_rng4_pk PRIMARY KEY(id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[10,11)'); --error
+ERROR:  value for domain int4_d violates check constraint "int4_d_check"
+LINE 1: INSERT INTO temporal_rng4 VALUES ('[1,2)', '[10,11)');
+                                                   ^
+INSERT INTO temporal_rng4 VALUES ('[10,11)', '[1,2)'); --error
+ERROR:  value for domain int4range_d violates check constraint "int4range_d_check"
+INSERT INTO temporal_rng4 VALUES ('[1,11)', '[9,10)'); --error
+ERROR:  value for domain int4_d violates check constraint "int4_d_check"
+LINE 1: INSERT INTO temporal_rng4 VALUES ('[1,11)', '[9,10)');
+                                                    ^
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[1,13)'), ('[1,2)', '[2,5)'); --error
+ERROR:  conflicting key value violates exclusion constraint "temporal_rng4_pk"
+DETAIL:  Key (id, valid_at)=([1,2), [2,5)) conflicts with existing key (id, valid_at)=([1,2), [1,13)).
+DROP TABLE temporal_rng4;
+--The domain is a range type, and its base type is a built-in range type.
+CREATE TABLE temporal_rng4 (
+  id int4range_d,
+  valid_at int4range_d,
+  CONSTRAINT temporal_rng4_pk UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[10,11)'); --error
+ERROR:  value for domain int4range_d violates check constraint "int4range_d_check"
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[3,13)'), ('[1,2)', '[2,13)'); --error
+ERROR:  conflicting key value violates exclusion constraint "temporal_rng4_pk"
+DETAIL:  Key (id, valid_at)=([1,2), [2,13)) conflicts with existing key (id, valid_at)=([1,2), [3,13)).
+DROP TABLE temporal_rng4;
+--The domain is a range type, and its base type is a user-defined range type.
+CREATE TABLE temporal_rng4 (
+  id int4range_d,
+  valid_at textrange2_dd,
+  CONSTRAINT temporal_rng4_pk UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4 VALUES ('[1,2)', NULL), (NULL, '[1,2)');
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[c,d)'); --error
+ERROR:  value for domain textrange2_dd violates check constraint "textrange2_d_check"
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[a,d)');
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[b,c)'); --error
+ERROR:  conflicting key value violates exclusion constraint "temporal_rng4_pk"
+DETAIL:  Key (id, valid_at)=([1,2), [b,c)) conflicts with existing key (id, valid_at)=([1,2), [a,d)).
+INSERT INTO temporal_rng4 VALUES ('[10,11)', NULL); --error
+ERROR:  value for domain int4range_d violates check constraint "int4range_d_check"
+INSERT INTO temporal_rng4 VALUES ('[2,3)', '[B,C)'), ('[2,3)', '[A,C)'); --error
+ERROR:  conflicting key value violates exclusion constraint "temporal_rng4_pk"
+DETAIL:  Key (id, valid_at)=([2,3), [A,C)) conflicts with existing key (id, valid_at)=([2,3), [B,C)).
+-- It is a multirange type rather than a domain type; however, the multirange
+-- type’s subtype is a domain type.
+CREATE TABLE temporal_mltrng4 (
+  id  int4range_d,
+  valid_at int4_d_multirange,
+  CONSTRAINT temporal_mltrng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[10,11)}'); --error
+ERROR:  value for domain int4_d violates check constraint "int4_d_check"
+LINE 1: INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[10,11)}');
+                                                      ^
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[3,13)}'), ('[1,2)', '{[2,13)}'); --error
+ERROR:  conflicting key value violates exclusion constraint "temporal_mltrng4_pk"
+DETAIL:  Key (id, valid_at)=([1,2), {[2,13)}) conflicts with existing key (id, valid_at)=([1,2), {[3,13)}).
+DROP TABLE temporal_mltrng4;
+--The domain is a multirange type, and its base type is a built-in multirange
+--type.
+CREATE TABLE temporal_mltrng4 (
+  id  int4range_d,
+  valid_at int4multirange_d,
+  CONSTRAINT temporal_mltrng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[10,11)}'); --error
+ERROR:  value for domain int4multirange_d violates check constraint "int4multirange_d_check"
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[3,13)}'), ('[1,2)', '{[2,13)}'); --error
+ERROR:  conflicting key value violates exclusion constraint "temporal_mltrng4_pk"
+DETAIL:  Key (id, valid_at)=([1,2), {[2,13)}) conflicts with existing key (id, valid_at)=([1,2), {[3,13)}).
+DROP TABLE temporal_mltrng4;
+--The domain is a multirange type, and its base type is a user-defined
+--multirange type.
+CREATE TABLE temporal_mltrng4 (
+  id int4range_d,
+  valid_at textmultirange2_d,
+  CONSTRAINT temporal_mltrng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[c,d)}'); --error
+ERROR:  value for domain textmultirange2_d violates check constraint "textmultirange2_d_check"
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[a,g)}');
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[b,c)}'); --error
+ERROR:  conflicting key value violates exclusion constraint "temporal_mltrng4_pk"
+DETAIL:  Key (id, valid_at)=([1,2), {[b,c)}) conflicts with existing key (id, valid_at)=([1,2), {[a,g)}).
+INSERT INTO temporal_mltrng4 VALUES ('[2,3)', '{[B,C)}'), ('[2,3)', '{[A,C)}'); --error
+ERROR:  conflicting key value violates exclusion constraint "temporal_mltrng4_pk"
+DETAIL:  Key (id, valid_at)=([2,3), {[A,C)}) conflicts with existing key (id, valid_at)=([2,3), {[B,C)}).
+----now test foreign key
+CREATE TABLE temporal_mltrngfk (
+  parent_id int4range_d,
+  id int4range,
+  valid_at textmultirange2_d);
+ALTER TABLE temporal_mltrngfk
+  ADD CONSTRAINT temporal_mltrngfk_fk
+    FOREIGN KEY (parent_id, PERIOD valid_at)
+    REFERENCES temporal_mltrng4;
+INSERT INTO temporal_mltrngfk VALUES ('[1,2)', '[2,3)', '{[d,e)}');
+UPDATE temporal_mltrng4 SET valid_at = '{[c,d)}'; --error
+ERROR:  value for domain textmultirange2_d violates check constraint "textmultirange2_d_check"
+UPDATE temporal_mltrng4 SET valid_at = '{[a,h)}';
+UPDATE temporal_mltrng4 SET valid_at = '{[f,g)}'; --error
+ERROR:  update or delete on table "temporal_mltrng4" violates foreign key constraint "temporal_mltrngfk_fk" on table "temporal_mltrngfk"
+DETAIL:  Key (id, valid_at)=([1,2), {[a,h)}) is still referenced from table "temporal_mltrngfk".
+DROP TABLE temporal_mltrng4, temporal_mltrngfk, temporal_rng4;
 -- PK with one column plus a multirange:
 CREATE TABLE temporal_mltrng (
   id int4range,
@@ -301,7 +423,6 @@ SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'te
 
 DROP TABLE temporal_rng3;
 -- UNIQUE with a custom range type:
-CREATE TYPE textrange2 AS range (subtype=text, collation="C");
 CREATE TABLE temporal_rng3 (
   id int4range,
   valid_at textrange2,
@@ -309,6 +430,9 @@ CREATE TABLE temporal_rng3 (
 );
 ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_uq;
 DROP TABLE temporal_rng3;
+DROP TYPE int4_d_range;
+DROP DOMAIN int4range_d, textrange2_dd, textrange2_d,
+            textmultirange2_d, int4multirange_d, int4_d;
 DROP TYPE textrange2;
 --
 -- test ALTER TABLE ADD CONSTRAINT
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index 4aaca242bbe..55b89b08622 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -100,7 +100,105 @@ CREATE TABLE temporal_rng3 (
 );
 ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_pk;
 DROP TABLE temporal_rng3;
-DROP TYPE textrange2;
+
+--
+-- tests for range over domain, multirange over a domain, custom range type over
+-- domain, custom multi range type over domain.
+--
+CREATE DOMAIN int4_d as integer check (value <> 10);
+CREATE TYPE int4_d_range as range (subtype = int4_d);
+CREATE DOMAIN int4multirange_d as int4multirange check (value <> '{[10,11)}');
+CREATE DOMAIN int4range_d AS int4range CHECK (VALUE <> '[10,11)');
+CREATE DOMAIN textrange2_d AS textrange2 CHECK (VALUE <> '[c,d)');
+CREATE DOMAIN textrange2_dd AS textrange2_d;
+CREATE DOMAIN textmultirange2_d AS textmultirange2 CHECK (VALUE <> '{[c,d)}');
+
+-- It is a range type rather than a domain type; however, the range type’s
+-- subtype is a domain type.
+CREATE TABLE temporal_rng4 (
+  id int4range_d,
+  valid_at int4_d_range,
+  CONSTRAINT temporal_rng4_pk PRIMARY KEY(id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[10,11)'); --error
+INSERT INTO temporal_rng4 VALUES ('[10,11)', '[1,2)'); --error
+INSERT INTO temporal_rng4 VALUES ('[1,11)', '[9,10)'); --error
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[1,13)'), ('[1,2)', '[2,5)'); --error
+DROP TABLE temporal_rng4;
+
+--The domain is a range type, and its base type is a built-in range type.
+CREATE TABLE temporal_rng4 (
+  id int4range_d,
+  valid_at int4range_d,
+  CONSTRAINT temporal_rng4_pk UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[10,11)'); --error
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[3,13)'), ('[1,2)', '[2,13)'); --error
+DROP TABLE temporal_rng4;
+
+--The domain is a range type, and its base type is a user-defined range type.
+CREATE TABLE temporal_rng4 (
+  id int4range_d,
+  valid_at textrange2_dd,
+  CONSTRAINT temporal_rng4_pk UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4 VALUES ('[1,2)', NULL), (NULL, '[1,2)');
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[c,d)'); --error
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[a,d)');
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[b,c)'); --error
+INSERT INTO temporal_rng4 VALUES ('[10,11)', NULL); --error
+INSERT INTO temporal_rng4 VALUES ('[2,3)', '[B,C)'), ('[2,3)', '[A,C)'); --error
+
+-- It is a multirange type rather than a domain type; however, the multirange
+-- type’s subtype is a domain type.
+CREATE TABLE temporal_mltrng4 (
+  id  int4range_d,
+  valid_at int4_d_multirange,
+  CONSTRAINT temporal_mltrng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[10,11)}'); --error
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[3,13)}'), ('[1,2)', '{[2,13)}'); --error
+DROP TABLE temporal_mltrng4;
+
+--The domain is a multirange type, and its base type is a built-in multirange
+--type.
+CREATE TABLE temporal_mltrng4 (
+  id  int4range_d,
+  valid_at int4multirange_d,
+  CONSTRAINT temporal_mltrng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[10,11)}'); --error
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[3,13)}'), ('[1,2)', '{[2,13)}'); --error
+DROP TABLE temporal_mltrng4;
+
+--The domain is a multirange type, and its base type is a user-defined
+--multirange type.
+CREATE TABLE temporal_mltrng4 (
+  id int4range_d,
+  valid_at textmultirange2_d,
+  CONSTRAINT temporal_mltrng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[c,d)}'); --error
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[a,g)}');
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[b,c)}'); --error
+INSERT INTO temporal_mltrng4 VALUES ('[2,3)', '{[B,C)}'), ('[2,3)', '{[A,C)}'); --error
+
+----now test foreign key
+CREATE TABLE temporal_mltrngfk (
+  parent_id int4range_d,
+  id int4range,
+  valid_at textmultirange2_d);
+ALTER TABLE temporal_mltrngfk
+  ADD CONSTRAINT temporal_mltrngfk_fk
+    FOREIGN KEY (parent_id, PERIOD valid_at)
+    REFERENCES temporal_mltrng4;
+
+INSERT INTO temporal_mltrngfk VALUES ('[1,2)', '[2,3)', '{[d,e)}');
+UPDATE temporal_mltrng4 SET valid_at = '{[c,d)}'; --error
+UPDATE temporal_mltrng4 SET valid_at = '{[a,h)}';
+UPDATE temporal_mltrng4 SET valid_at = '{[f,g)}'; --error
+
+DROP TABLE temporal_mltrng4, temporal_mltrngfk, temporal_rng4;
 
 -- PK with one column plus a multirange:
 CREATE TABLE temporal_mltrng (
@@ -170,7 +268,6 @@ SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'te
 DROP TABLE temporal_rng3;
 
 -- UNIQUE with a custom range type:
-CREATE TYPE textrange2 AS range (subtype=text, collation="C");
 CREATE TABLE temporal_rng3 (
   id int4range,
   valid_at textrange2,
@@ -178,6 +275,9 @@ CREATE TABLE temporal_rng3 (
 );
 ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_uq;
 DROP TABLE temporal_rng3;
+DROP TYPE int4_d_range;
+DROP DOMAIN int4range_d, textrange2_dd, textrange2_d,
+            textmultirange2_d, int4multirange_d, int4_d;
 DROP TYPE textrange2;
 
 --
-- 
2.34.1



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: domain for WITHOUT OVERLAPS
  2025-12-03 07:38 domain for WITHOUT OVERLAPS jian he <[email protected]>
  2025-12-03 18:39 ` Re: domain for WITHOUT OVERLAPS Paul A Jungwirth <[email protected]>
  2025-12-11 04:23   ` Re: domain for WITHOUT OVERLAPS jian he <[email protected]>
  2025-12-23 03:25     ` Re: domain for WITHOUT OVERLAPS Paul A Jungwirth <[email protected]>
  2025-12-29 07:17       ` Re: domain for WITHOUT OVERLAPS jian he <[email protected]>
@ 2026-03-10 07:41         ` jian he <[email protected]>
  2026-03-10 16:31           ` Re: domain for WITHOUT OVERLAPS Paul A Jungwirth <[email protected]>
  1 sibling, 1 reply; 11+ messages in thread

From: jian he @ 2026-03-10 07:41 UTC (permalink / raw)
  To: Paul A Jungwirth <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>

Hi.

V4 looks good to me.

One minor question regarding comments in transformIndexConstraint:
/*
 * The WITHOUT OVERLAPS part (if any) must be a range or
 * multirange type.
 */

Should we change it to:

/*
 * The WITHOUT OVERLAPS part (if any) base type must be a range or
 * multirange type.
 */



--
jian
https://www.enterprisedb.com/





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: domain for WITHOUT OVERLAPS
  2025-12-03 07:38 domain for WITHOUT OVERLAPS jian he <[email protected]>
  2025-12-03 18:39 ` Re: domain for WITHOUT OVERLAPS Paul A Jungwirth <[email protected]>
  2025-12-11 04:23   ` Re: domain for WITHOUT OVERLAPS jian he <[email protected]>
  2025-12-23 03:25     ` Re: domain for WITHOUT OVERLAPS Paul A Jungwirth <[email protected]>
  2025-12-29 07:17       ` Re: domain for WITHOUT OVERLAPS jian he <[email protected]>
  2026-03-10 07:41         ` Re: domain for WITHOUT OVERLAPS jian he <[email protected]>
@ 2026-03-10 16:31           ` Paul A Jungwirth <[email protected]>
  0 siblings, 0 replies; 11+ messages in thread

From: Paul A Jungwirth @ 2026-03-10 16:31 UTC (permalink / raw)
  To: jian he <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>

On Tue, Mar 10, 2026 at 12:42 AM jian he <[email protected]> wrote:
>
> Hi.
>
> V4 looks good to me.

Thanks for taking a look!

> One minor question regarding comments in transformIndexConstraint:
> /*
>  * The WITHOUT OVERLAPS part (if any) must be a range or
>  * multirange type.
>  */
>
> Should we change it to:
>
> /*
>  * The WITHOUT OVERLAPS part (if any) base type must be a range or
>  * multirange type.
>  */

Sure. Here is an updated patch. It has some comment adjustments based
on Kirill's feedback too.

Yours,

--
Paul              ~{:-)
[email protected]


Attachments:

  [application/octet-stream] v5-0001-Add-support-for-domain-types-in-WITHOUT-OVERLAPS.patch (22.1K, 2-v5-0001-Add-support-for-domain-types-in-WITHOUT-OVERLAPS.patch)
  download | inline diff:
From 9cc881747c687da12785e090bc56a32f20bb021b Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 29 Dec 2025 15:15:23 +0800
Subject: [PATCH v5] Add support for domain types in WITHOUT OVERLAPS

This commit allows domain types to work with the WITHOUT OVERLAPS constraint.
Domains over range types and domains over multirange types are both supported.
User-defined range types whose subtype is a domain are also supported.

commitfest: https://commitfest.postgresql.org/patch/6281
discussion: https://postgr.es/m/CACJufxGoAmN_0iJ=hjTG0vGpOSOyy-vYyfE+-q0AWxrq2_p5XQ@mail.gmail.com
---
 src/backend/executor/execIndexing.c           |  10 +-
 src/backend/parser/parse_utilcmd.c            |   4 +-
 src/backend/utils/cache/typcache.c            |   2 +
 src/include/utils/typcache.h                  |   5 +-
 .../regress/expected/without_overlaps.out     | 178 +++++++++++++++++-
 src/test/regress/sql/without_overlaps.sql     | 152 ++++++++++++++-
 6 files changed, 342 insertions(+), 9 deletions(-)

diff --git a/src/backend/executor/execIndexing.c b/src/backend/executor/execIndexing.c
index 9d071e495c6..984b859c76f 100644
--- a/src/backend/executor/execIndexing.c
+++ b/src/backend/executor/execIndexing.c
@@ -751,13 +751,19 @@ check_exclusion_or_unique_constraint(Relation heap, Relation index,
 
 		if (!isnull[indnkeyatts - 1])
 		{
+			char 		typtype;
 			TupleDesc	tupdesc = RelationGetDescr(heap);
 			Form_pg_attribute att = TupleDescAttr(tupdesc, attno - 1);
-			TypeCacheEntry *typcache = lookup_type_cache(att->atttypid, 0);
+			TypeCacheEntry *typcache = lookup_type_cache(att->atttypid, TYPECACHE_DOMAIN_BASE_INFO);
+
+			if (OidIsValid(typcache->domainBaseType))
+				typtype = typcache->domainBaseTyptype;
+			else
+				typtype = typcache->typtype;
 
 			ExecWithoutOverlapsNotEmpty(heap, att->attname,
 										values[indnkeyatts - 1],
-										typcache->typtype, att->atttypid);
+										typtype, att->atttypid);
 		}
 	}
 
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index cc244c49e9e..43c49b85ed5 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -2764,7 +2764,7 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
 			}
 
 			/*
-			 * The WITHOUT OVERLAPS part (if any) must be a range or
+			 * The WITHOUT OVERLAPS part (if any) base type must be a range or
 			 * multirange type.
 			 */
 			if (constraint->without_overlaps && lc == list_last_cell(constraint->keys))
@@ -2799,6 +2799,8 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
 					if (!OidIsValid(typid) && column)
 						typid = typenameTypeId(NULL, column->typeName);
 
+					typid = getBaseType(typid);
+
 					if (!OidIsValid(typid) || !(type_is_range(typid) || type_is_multirange(typid)))
 						ereport(ERROR,
 								(errcode(ERRCODE_DATATYPE_MISMATCH),
diff --git a/src/backend/utils/cache/typcache.c b/src/backend/utils/cache/typcache.c
index 627e534609a..e09e5b4d4e1 100644
--- a/src/backend/utils/cache/typcache.c
+++ b/src/backend/utils/cache/typcache.c
@@ -947,6 +947,8 @@ lookup_type_cache(Oid type_id, int flags)
 		typentry->domainBaseTypmod = -1;
 		typentry->domainBaseType =
 			getBaseTypeAndTypmod(type_id, &typentry->domainBaseTypmod);
+		typentry->domainBaseTyptype =
+			get_typtype(typentry->domainBaseType);
 	}
 	if ((flags & TYPECACHE_DOMAIN_CONSTR_INFO) &&
 		(typentry->flags & TCFLAGS_CHECKED_DOMAIN_CONSTRAINTS) == 0 &&
diff --git a/src/include/utils/typcache.h b/src/include/utils/typcache.h
index 0e3945aa244..40708621afe 100644
--- a/src/include/utils/typcache.h
+++ b/src/include/utils/typcache.h
@@ -109,11 +109,12 @@ typedef struct TypeCacheEntry
 	struct TypeCacheEntry *rngtype; /* multirange's range underlying type */
 
 	/*
-	 * Domain's base type and typmod if it's a domain type.  Zeroes if not
-	 * domain, or if information hasn't been requested.
+	 * Domain's base type, typmod, and typtype if it's a domain type.
+	 * Zeroes if not domain, or if information hasn't been requested.
 	 */
 	Oid			domainBaseType;
 	int32		domainBaseTypmod;
+	char		domainBaseTyptype;
 
 	/*
 	 * Domain constraint data if it's a domain type.  NULL if not domain, or
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index 06f6fd2c8c5..6de444d4620 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -162,7 +162,179 @@ CREATE TABLE temporal_rng3 (
 );
 ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_pk;
 DROP TABLE temporal_rng3;
-DROP TYPE textrange2;
+--
+-- test PRIMARY KEY and UNIQUE constraint interaction with domains:
+--
+CREATE DOMAIN int4_d as integer check (value <> 10);
+-- range over domain:
+CREATE TYPE int4_d_range as range (subtype = int4_d);
+-- domain over built-in range:
+CREATE DOMAIN int4range_d AS int4range CHECK (VALUE <> '[10,11)');
+-- domain over custom range (not tested directly):
+CREATE DOMAIN textrange2_d AS textrange2 CHECK (VALUE <> '[c,d)');
+-- domain over domain over custom range:
+CREATE DOMAIN textrange2_dd AS textrange2_d;
+-- domain over built-in multirange:
+CREATE DOMAIN int4multirange_d as int4multirange check (value <> '{[10,11)}');
+-- domain over custom multirange:
+CREATE DOMAIN textmultirange2_d AS textmultirange2 CHECK (VALUE <> '{[c,d)}');
+-- We also test a multirange over a domain subtype (below).
+-- We can't test a multirange whose rangetype is a domain,
+-- because defining a domain on a rangetype doesn't create a new multirangetype
+-- (or new range constructors, pg_range entry, etc.).
+-- The range's subtype is a domain:
+CREATE TABLE temporal_rng4 (
+  id int4range,
+  valid_at int4_d_range,
+  CONSTRAINT temporal_rng4_pk PRIMARY KEY(id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4 VALUES ('[1,11)', '[9,10)'); -- start bound violates domain
+ERROR:  value for domain int4_d violates check constraint "int4_d_check"
+LINE 1: INSERT INTO temporal_rng4 VALUES ('[1,11)', '[9,10)');
+                                                    ^
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[10,11)'); -- end bound violates domain
+ERROR:  value for domain int4_d violates check constraint "int4_d_check"
+LINE 1: INSERT INTO temporal_rng4 VALUES ('[1,2)', '[10,11)');
+                                                   ^
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[1,13)'), ('[1,2)', '[2,5)'); -- overlaps
+ERROR:  conflicting key value violates exclusion constraint "temporal_rng4_pk"
+DETAIL:  Key (id, valid_at)=([1,2), [2,5)) conflicts with existing key (id, valid_at)=([1,2), [1,13)).
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[1,13)'), ('[1,2)', '[20,23)'); -- okay
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[30,)'); -- null bound is okay
+DROP TABLE temporal_rng4;
+-- The domain is over a built-in range type:
+CREATE TABLE temporal_rng4 (
+  id int4range,
+  valid_at int4range_d,
+  CONSTRAINT temporal_rng4_pk UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[10,11)'); -- violates domain
+ERROR:  value for domain int4range_d violates check constraint "int4range_d_check"
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[1,13)'), ('[1,2)', '[2,13)'); -- overlaps
+ERROR:  conflicting key value violates exclusion constraint "temporal_rng4_pk"
+DETAIL:  Key (id, valid_at)=([1,2), [2,13)) conflicts with existing key (id, valid_at)=([1,2), [1,13)).
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[1,13)'), ('[1,2)', '[20,23)'); -- okay
+DROP TABLE temporal_rng4;
+-- The domain is over another domain which is over a user-defined range type:
+CREATE TABLE temporal_rng4 (
+  id int4range,
+  valid_at textrange2_dd,
+  CONSTRAINT temporal_rng4_pk UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4 VALUES ('[1,2)', NULL), (NULL, '[1,2)');
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[c,d)'); -- violates domain
+ERROR:  value for domain textrange2_dd violates check constraint "textrange2_d_check"
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[a,d)'); -- okay
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[b,c)'); -- overlaps
+ERROR:  conflicting key value violates exclusion constraint "temporal_rng4_pk"
+DETAIL:  Key (id, valid_at)=([1,2), [b,c)) conflicts with existing key (id, valid_at)=([1,2), [a,d)).
+INSERT INTO temporal_rng4 VALUES ('[2,3)', '[B,C)'), ('[2,3)', '[A,C)'); -- overlaps
+ERROR:  conflicting key value violates exclusion constraint "temporal_rng4_pk"
+DETAIL:  Key (id, valid_at)=([2,3), [A,C)) conflicts with existing key (id, valid_at)=([2,3), [B,C)).
+-- It is a multirange type rather than a domain type; however, the multirange
+-- type’s subtype is a domain type.
+CREATE TABLE temporal_mltrng4 (
+  id  int4range,
+  valid_at int4_d_multirange,
+  CONSTRAINT temporal_mltrng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[10,11)}'); -- violates domain
+ERROR:  value for domain int4_d violates check constraint "int4_d_check"
+LINE 1: INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[10,11)}');
+                                                      ^
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[1,13)}'), ('[1,2)', '{[2,13)}'); -- overlaps
+ERROR:  conflicting key value violates exclusion constraint "temporal_mltrng4_pk"
+DETAIL:  Key (id, valid_at)=([1,2), {[2,13)}) conflicts with existing key (id, valid_at)=([1,2), {[1,13)}).
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[1,13)}'), ('[1,2)', '{[20,23)}'); -- okay
+DROP TABLE temporal_mltrng4;
+-- The domain is over a built-in multirange type.
+CREATE TABLE temporal_mltrng4 (
+  id  int4range,
+  valid_at int4multirange_d,
+  CONSTRAINT temporal_mltrng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[10,11)}'); -- violates domain
+ERROR:  value for domain int4multirange_d violates check constraint "int4multirange_d_check"
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[1,13)}'), ('[1,2)', '{[2,13)}'); -- overlaps
+ERROR:  conflicting key value violates exclusion constraint "temporal_mltrng4_pk"
+DETAIL:  Key (id, valid_at)=([1,2), {[2,13)}) conflicts with existing key (id, valid_at)=([1,2), {[1,13)}).
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[1,13)}'), ('[1,2)', '{[20,23)}'); -- okay
+DROP TABLE temporal_mltrng4;
+-- The domain is over a user-defined multirange type.
+CREATE TABLE temporal_mltrng4 (
+  id int4range,
+  valid_at textmultirange2_d,
+  CONSTRAINT temporal_mltrng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[c,d)}'); -- violates domain
+ERROR:  value for domain textmultirange2_d violates check constraint "textmultirange2_d_check"
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[a,g)}'); -- okay
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[b,c)}'); -- overlaps
+ERROR:  conflicting key value violates exclusion constraint "temporal_mltrng4_pk"
+DETAIL:  Key (id, valid_at)=([1,2), {[b,c)}) conflicts with existing key (id, valid_at)=([1,2), {[a,g)}).
+INSERT INTO temporal_mltrng4 VALUES ('[2,3)', '{[B,C)}'), ('[2,3)', '{[A,C)}'); -- overlaps
+ERROR:  conflicting key value violates exclusion constraint "temporal_mltrng4_pk"
+DETAIL:  Key (id, valid_at)=([2,3), {[A,C)}) conflicts with existing key (id, valid_at)=([2,3), {[B,C)}).
+-- test foreign key interactions with domains:
+-- domain references domain:
+UPDATE temporal_mltrng4 SET valid_at = '{[a,g)}'; -- reset
+CREATE TABLE temporal_mltrngfk (
+  parent_id int4range,
+  id int4range,
+  valid_at textmultirange2_d,
+  CONSTRAINT temporal_mltrngfk_fk
+    FOREIGN KEY (parent_id, PERIOD valid_at)
+    REFERENCES temporal_mltrng4
+);
+INSERT INTO temporal_mltrngfk VALUES ('[1,2)', '[2,3)', '{[d,e)}');
+UPDATE temporal_mltrng4 SET valid_at = '{[c,d)}'; -- violates domain
+ERROR:  value for domain textmultirange2_d violates check constraint "textmultirange2_d_check"
+UPDATE temporal_mltrng4 SET valid_at = '{[a,h)}'; -- okay
+UPDATE temporal_mltrng4 SET valid_at = '{[f,g)}'; -- violates reference
+ERROR:  update or delete on table "temporal_mltrng4" violates foreign key constraint "temporal_mltrngfk_fk" on table "temporal_mltrngfk"
+DETAIL:  Key (id, valid_at)=([1,2), {[a,h)}) is still referenced from table "temporal_mltrngfk".
+DROP TABLE temporal_mltrngfk;
+-- non-domain references domain:
+UPDATE temporal_mltrng4 SET valid_at = '{[a,g)}'; -- reset
+CREATE TABLE temporal_mltrngfk (
+  parent_id int4range,
+  id int4range,
+  valid_at textmultirange2,
+  CONSTRAINT temporal_mltrngfk_fk
+    FOREIGN KEY (parent_id, PERIOD valid_at)
+    REFERENCES temporal_mltrng4
+);
+INSERT INTO temporal_mltrngfk VALUES ('[1,2)', '[2,3)', '{[d,e)}');
+UPDATE temporal_mltrng4 SET valid_at = '{[c,d)}'; -- violates domain
+ERROR:  value for domain textmultirange2_d violates check constraint "textmultirange2_d_check"
+UPDATE temporal_mltrng4 SET valid_at = '{[a,h)}'; -- okay
+UPDATE temporal_mltrng4 SET valid_at = '{[f,g)}'; -- violates reference
+ERROR:  update or delete on table "temporal_mltrng4" violates foreign key constraint "temporal_mltrngfk_fk" on table "temporal_mltrngfk"
+DETAIL:  Key (id, valid_at)=([1,2), {[a,h)}) is still referenced from table "temporal_mltrngfk".
+DROP TABLE temporal_mltrngfk;
+-- domain references non-domain:
+CREATE TABLE temporal_mltrng3 (
+  id int4range,
+  valid_at textmultirange2
+);
+INSERT INTO temporal_mltrng3 VALUES ('[1,2)', '{[a,g)}');
+CREATE TABLE temporal_mltrngfk (
+  id int4range,
+  parent_id int4range,
+  valid_at textmultirange2_d,
+  CONSTRAINT temporal_mltrngfk_fk
+    FOREIGN KEY (parent_id, PERIOD valid_at)
+    REFERENCES temporal_mltrng4
+);
+INSERT INTO temporal_mltrngfk VALUES ('[1,2)', '[1,2)', '{[d,e)}');
+UPDATE temporal_mltrng4 SET valid_at = '{[c,d)}'; -- violates domain
+ERROR:  value for domain textmultirange2_d violates check constraint "textmultirange2_d_check"
+UPDATE temporal_mltrng4 SET valid_at = '{[a,h)}'; -- okay
+UPDATE temporal_mltrng4 SET valid_at = '{[f,g)}'; -- violates reference
+ERROR:  update or delete on table "temporal_mltrng4" violates foreign key constraint "temporal_mltrngfk_fk" on table "temporal_mltrngfk"
+DETAIL:  Key (id, valid_at)=([1,2), {[a,h)}) is still referenced from table "temporal_mltrngfk".
+DROP TABLE temporal_mltrng3, temporal_mltrngfk;
+DROP TABLE temporal_mltrng4, temporal_rng4;
 -- PK with one column plus a multirange:
 CREATE TABLE temporal_mltrng (
   id int4range,
@@ -301,7 +473,6 @@ SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'te
 
 DROP TABLE temporal_rng3;
 -- UNIQUE with a custom range type:
-CREATE TYPE textrange2 AS range (subtype=text, collation="C");
 CREATE TABLE temporal_rng3 (
   id int4range,
   valid_at textrange2,
@@ -309,6 +480,9 @@ CREATE TABLE temporal_rng3 (
 );
 ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_uq;
 DROP TABLE temporal_rng3;
+DROP TYPE int4_d_range;
+DROP DOMAIN int4range_d, textrange2_dd, textrange2_d,
+            textmultirange2_d, int4multirange_d, int4_d;
 DROP TYPE textrange2;
 --
 -- test ALTER TABLE ADD CONSTRAINT
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index 77be6953575..423419e11f1 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -100,7 +100,153 @@ CREATE TABLE temporal_rng3 (
 );
 ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_pk;
 DROP TABLE temporal_rng3;
-DROP TYPE textrange2;
+
+--
+-- test PRIMARY KEY and UNIQUE constraint interaction with domains:
+--
+CREATE DOMAIN int4_d as integer check (value <> 10);
+-- range over domain:
+CREATE TYPE int4_d_range as range (subtype = int4_d);
+-- domain over built-in range:
+CREATE DOMAIN int4range_d AS int4range CHECK (VALUE <> '[10,11)');
+-- domain over custom range (not tested directly):
+CREATE DOMAIN textrange2_d AS textrange2 CHECK (VALUE <> '[c,d)');
+-- domain over domain over custom range:
+CREATE DOMAIN textrange2_dd AS textrange2_d;
+-- domain over built-in multirange:
+CREATE DOMAIN int4multirange_d as int4multirange check (value <> '{[10,11)}');
+-- domain over custom multirange:
+CREATE DOMAIN textmultirange2_d AS textmultirange2 CHECK (VALUE <> '{[c,d)}');
+-- We also test a multirange over a domain subtype (below).
+-- We can't test a multirange whose rangetype is a domain,
+-- because defining a domain on a rangetype doesn't create a new multirangetype
+-- (or new range constructors, pg_range entry, etc.).
+
+-- The range's subtype is a domain:
+CREATE TABLE temporal_rng4 (
+  id int4range,
+  valid_at int4_d_range,
+  CONSTRAINT temporal_rng4_pk PRIMARY KEY(id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4 VALUES ('[1,11)', '[9,10)'); -- start bound violates domain
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[10,11)'); -- end bound violates domain
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[1,13)'), ('[1,2)', '[2,5)'); -- overlaps
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[1,13)'), ('[1,2)', '[20,23)'); -- okay
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[30,)'); -- null bound is okay
+DROP TABLE temporal_rng4;
+
+-- The domain is over a built-in range type:
+CREATE TABLE temporal_rng4 (
+  id int4range,
+  valid_at int4range_d,
+  CONSTRAINT temporal_rng4_pk UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[10,11)'); -- violates domain
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[1,13)'), ('[1,2)', '[2,13)'); -- overlaps
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[1,13)'), ('[1,2)', '[20,23)'); -- okay
+DROP TABLE temporal_rng4;
+
+-- The domain is over another domain which is over a user-defined range type:
+CREATE TABLE temporal_rng4 (
+  id int4range,
+  valid_at textrange2_dd,
+  CONSTRAINT temporal_rng4_pk UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4 VALUES ('[1,2)', NULL), (NULL, '[1,2)');
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[c,d)'); -- violates domain
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[a,d)'); -- okay
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[b,c)'); -- overlaps
+INSERT INTO temporal_rng4 VALUES ('[2,3)', '[B,C)'), ('[2,3)', '[A,C)'); -- overlaps
+
+-- It is a multirange type rather than a domain type; however, the multirange
+-- type’s subtype is a domain type.
+CREATE TABLE temporal_mltrng4 (
+  id  int4range,
+  valid_at int4_d_multirange,
+  CONSTRAINT temporal_mltrng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[10,11)}'); -- violates domain
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[1,13)}'), ('[1,2)', '{[2,13)}'); -- overlaps
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[1,13)}'), ('[1,2)', '{[20,23)}'); -- okay
+DROP TABLE temporal_mltrng4;
+
+-- The domain is over a built-in multirange type.
+CREATE TABLE temporal_mltrng4 (
+  id  int4range,
+  valid_at int4multirange_d,
+  CONSTRAINT temporal_mltrng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[10,11)}'); -- violates domain
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[1,13)}'), ('[1,2)', '{[2,13)}'); -- overlaps
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[1,13)}'), ('[1,2)', '{[20,23)}'); -- okay
+DROP TABLE temporal_mltrng4;
+
+-- The domain is over a user-defined multirange type.
+CREATE TABLE temporal_mltrng4 (
+  id int4range,
+  valid_at textmultirange2_d,
+  CONSTRAINT temporal_mltrng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[c,d)}'); -- violates domain
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[a,g)}'); -- okay
+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[b,c)}'); -- overlaps
+INSERT INTO temporal_mltrng4 VALUES ('[2,3)', '{[B,C)}'), ('[2,3)', '{[A,C)}'); -- overlaps
+
+-- test foreign key interactions with domains:
+
+-- domain references domain:
+UPDATE temporal_mltrng4 SET valid_at = '{[a,g)}'; -- reset
+CREATE TABLE temporal_mltrngfk (
+  parent_id int4range,
+  id int4range,
+  valid_at textmultirange2_d,
+  CONSTRAINT temporal_mltrngfk_fk
+    FOREIGN KEY (parent_id, PERIOD valid_at)
+    REFERENCES temporal_mltrng4
+);
+INSERT INTO temporal_mltrngfk VALUES ('[1,2)', '[2,3)', '{[d,e)}');
+UPDATE temporal_mltrng4 SET valid_at = '{[c,d)}'; -- violates domain
+UPDATE temporal_mltrng4 SET valid_at = '{[a,h)}'; -- okay
+UPDATE temporal_mltrng4 SET valid_at = '{[f,g)}'; -- violates reference
+DROP TABLE temporal_mltrngfk;
+
+-- non-domain references domain:
+UPDATE temporal_mltrng4 SET valid_at = '{[a,g)}'; -- reset
+CREATE TABLE temporal_mltrngfk (
+  parent_id int4range,
+  id int4range,
+  valid_at textmultirange2,
+  CONSTRAINT temporal_mltrngfk_fk
+    FOREIGN KEY (parent_id, PERIOD valid_at)
+    REFERENCES temporal_mltrng4
+);
+INSERT INTO temporal_mltrngfk VALUES ('[1,2)', '[2,3)', '{[d,e)}');
+UPDATE temporal_mltrng4 SET valid_at = '{[c,d)}'; -- violates domain
+UPDATE temporal_mltrng4 SET valid_at = '{[a,h)}'; -- okay
+UPDATE temporal_mltrng4 SET valid_at = '{[f,g)}'; -- violates reference
+DROP TABLE temporal_mltrngfk;
+
+-- domain references non-domain:
+CREATE TABLE temporal_mltrng3 (
+  id int4range,
+  valid_at textmultirange2
+);
+INSERT INTO temporal_mltrng3 VALUES ('[1,2)', '{[a,g)}');
+CREATE TABLE temporal_mltrngfk (
+  id int4range,
+  parent_id int4range,
+  valid_at textmultirange2_d,
+  CONSTRAINT temporal_mltrngfk_fk
+    FOREIGN KEY (parent_id, PERIOD valid_at)
+    REFERENCES temporal_mltrng4
+);
+INSERT INTO temporal_mltrngfk VALUES ('[1,2)', '[1,2)', '{[d,e)}');
+UPDATE temporal_mltrng4 SET valid_at = '{[c,d)}'; -- violates domain
+UPDATE temporal_mltrng4 SET valid_at = '{[a,h)}'; -- okay
+UPDATE temporal_mltrng4 SET valid_at = '{[f,g)}'; -- violates reference
+DROP TABLE temporal_mltrng3, temporal_mltrngfk;
+
+DROP TABLE temporal_mltrng4, temporal_rng4;
 
 -- PK with one column plus a multirange:
 CREATE TABLE temporal_mltrng (
@@ -170,7 +316,6 @@ SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'te
 DROP TABLE temporal_rng3;
 
 -- UNIQUE with a custom range type:
-CREATE TYPE textrange2 AS range (subtype=text, collation="C");
 CREATE TABLE temporal_rng3 (
   id int4range,
   valid_at textrange2,
@@ -178,6 +323,9 @@ CREATE TABLE temporal_rng3 (
 );
 ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_uq;
 DROP TABLE temporal_rng3;
+DROP TYPE int4_d_range;
+DROP DOMAIN int4range_d, textrange2_dd, textrange2_d,
+            textmultirange2_d, int4multirange_d, int4_d;
 DROP TYPE textrange2;
 
 --
-- 
2.45.0



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: domain for WITHOUT OVERLAPS
  2025-12-03 07:38 domain for WITHOUT OVERLAPS jian he <[email protected]>
  2025-12-03 18:39 ` Re: domain for WITHOUT OVERLAPS Paul A Jungwirth <[email protected]>
  2025-12-11 04:23   ` Re: domain for WITHOUT OVERLAPS jian he <[email protected]>
  2025-12-23 03:25     ` Re: domain for WITHOUT OVERLAPS Paul A Jungwirth <[email protected]>
  2025-12-29 07:17       ` Re: domain for WITHOUT OVERLAPS jian he <[email protected]>
@ 2026-03-10 09:53         ` Kirill Reshke <[email protected]>
  2026-03-10 16:33           ` Re: domain for WITHOUT OVERLAPS Paul A Jungwirth <[email protected]>
  1 sibling, 1 reply; 11+ messages in thread

From: Kirill Reshke @ 2026-03-10 09:53 UTC (permalink / raw)
  To: Paul A Jungwirth <[email protected]>; +Cc: jian he <[email protected]>; PostgreSQL Hackers <[email protected]>

On Wed, 11 Feb 2026 at 23:51, Paul A Jungwirth
<[email protected]> wrote:
>
> On Sun, Dec 28, 2025 at 11:18 PM jian he <[email protected]> wrote:
> >
> > > Is there any performance concern about adding this lookup?
> > > From what I can tell we don't use TYPECACHE_DOMAIN_BASE_INFO very
> > > often, so it is probably okay.
> > > We can get here in the executor from ExecEvalWholeRowVar, but that
> > > seems acceptable to me.
> > >
> > >
> > hi.
> >
> > To make WITHOUT OVERLAPS work with domains, ExecWithoutOverlapsNotEmpty requires
> > the base type’s typtype. I do not see a viable alternative approach.
> >
> > We can cache the base type's typtype in TypeCacheEntry->domainBaseTyptype.
>
> Okay.
>
> > >
> > > Also testing a range over a domain (in WITHOUT OVERLAPS position) would be good.
> > >
> > I do not think we need extensive foreign key–related tests, because
> > check_exclusion_or_unique_constraint is only invoked for data changes on the
> > primary key side. So, I kept only a single foreign key–related test.
>
> The foreign key code also uses the column type, so there are still
> interesting feature combinations here that should be tested rather
> than just assuming they work.
>
> > The attached patch should address all of your comments.
> > I think you will like attached regress tests.
>
> A lot of the issues I mentioned before don't look addressed to me.
> What do you think of the v4 patch attached here? It includes a few
> more FK tests. Some other details I changed:
>
> +--
> +-- tests for range over domain, multirange over a domain, custom
> range type over
> +-- domain, custom multi range type over domain.
> +--
> +CREATE DOMAIN int4_d as integer check (value <> 10);
> +CREATE TYPE int4_d_range as range (subtype = int4_d);
> +CREATE DOMAIN int4multirange_d as int4multirange check (value <> '{[10,11)}');
> +CREATE DOMAIN int4range_d AS int4range CHECK (VALUE <> '[10,11)');
> +CREATE DOMAIN textrange2_d AS textrange2 CHECK (VALUE <> '[c,d)');
> +CREATE DOMAIN textrange2_dd AS textrange2_d;
> +CREATE DOMAIN textmultirange2_d AS textmultirange2 CHECK (VALUE <> '{[c,d)}');
>
> This comment still doesn't match the types created below it. In my v4
> patch I've added a comment before each CREATE TYPE/DOMAIN line.
>
> Also let's define these in the same order that we test them below.
>
> +-- It is a range type rather than a domain type; however, the range type’s
> +-- subtype is a domain type.
> +CREATE TABLE temporal_rng4 (
> +  id int4range_d,
> +  valid_at int4_d_range,
> +  CONSTRAINT temporal_rng4_pk PRIMARY KEY(id, valid_at WITHOUT OVERLAPS)
> +);
> +INSERT INTO temporal_rng4 VALUES ('[1,2)', '[10,11)'); --error
> +INSERT INTO temporal_rng4 VALUES ('[10,11)', '[1,2)'); --error
> +INSERT INTO temporal_rng4 VALUES ('[1,11)', '[9,10)'); --error
> +INSERT INTO temporal_rng4 VALUES ('[1,2)', '[1,13)'), ('[1,2)',
> '[2,5)'); --error
> +DROP TABLE temporal_rng4;
>
> We need to test a case that succeeds, not just cases that fail. Also
> the last case doesn't violate the domain; it just conflicts with a
> prior row. That's a good test too, but let's clarify.
>
> The point is to test WITHOUT OVERLAPS, but several of these lines are
> testing the id column, not the valid_at column. I made the id columns
> be just regular int4range.
>
> Likewise with the scenarios below.
>
> Yours,
>
> --
> Paul              ~{:-)
> [email protected]


Hi!
I v4, test comments says we check for unique violation, while test
output says its exclusion constraint:

+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[1,13)}'), ('[1,2)',
'{[2,13)}'); -- not unique
+ERROR:  conflicting key value violates exclusion constraint
"temporal_mltrng4_pk"

Maybe we can update wording to be more precise? Like `-- overlaps with
other tuple`.

Other that this nit, patch looks good.

-- 
Best regards,
Kirill Reshke





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: domain for WITHOUT OVERLAPS
  2025-12-03 07:38 domain for WITHOUT OVERLAPS jian he <[email protected]>
  2025-12-03 18:39 ` Re: domain for WITHOUT OVERLAPS Paul A Jungwirth <[email protected]>
  2025-12-11 04:23   ` Re: domain for WITHOUT OVERLAPS jian he <[email protected]>
  2025-12-23 03:25     ` Re: domain for WITHOUT OVERLAPS Paul A Jungwirth <[email protected]>
  2025-12-29 07:17       ` Re: domain for WITHOUT OVERLAPS jian he <[email protected]>
  2026-03-10 09:53         ` Re: domain for WITHOUT OVERLAPS Kirill Reshke <[email protected]>
@ 2026-03-10 16:33           ` Paul A Jungwirth <[email protected]>
  2026-04-07 18:57             ` Re: domain for WITHOUT OVERLAPS Tom Lane <[email protected]>
  0 siblings, 1 reply; 11+ messages in thread

From: Paul A Jungwirth @ 2026-03-10 16:33 UTC (permalink / raw)
  To: Kirill Reshke <[email protected]>; +Cc: jian he <[email protected]>; PostgreSQL Hackers <[email protected]>

On Tue, Mar 10, 2026 at 2:53 AM Kirill Reshke <[email protected]> wrote:
>
> Hi!
> I v4, test comments says we check for unique violation, while test
> output says its exclusion constraint:
>
> +INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[1,13)}'), ('[1,2)',
> '{[2,13)}'); -- not unique
> +ERROR:  conflicting key value violates exclusion constraint
> "temporal_mltrng4_pk"
>
> Maybe we can update wording to be more precise? Like `-- overlaps with
> other tuple`.
>
> Other that this nit, patch looks good.

Thanks for taking a look! I changed places we say `-- not unique` to
now say `-- overlaps`. v5 is attached to my reply to jian he.

Yours,

-- 
Paul              ~{:-)
[email protected]





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: domain for WITHOUT OVERLAPS
  2025-12-03 07:38 domain for WITHOUT OVERLAPS jian he <[email protected]>
  2025-12-03 18:39 ` Re: domain for WITHOUT OVERLAPS Paul A Jungwirth <[email protected]>
  2025-12-11 04:23   ` Re: domain for WITHOUT OVERLAPS jian he <[email protected]>
  2025-12-23 03:25     ` Re: domain for WITHOUT OVERLAPS Paul A Jungwirth <[email protected]>
  2025-12-29 07:17       ` Re: domain for WITHOUT OVERLAPS jian he <[email protected]>
  2026-03-10 09:53         ` Re: domain for WITHOUT OVERLAPS Kirill Reshke <[email protected]>
  2026-03-10 16:33           ` Re: domain for WITHOUT OVERLAPS Paul A Jungwirth <[email protected]>
@ 2026-04-07 18:57             ` Tom Lane <[email protected]>
  0 siblings, 0 replies; 11+ messages in thread

From: Tom Lane @ 2026-04-07 18:57 UTC (permalink / raw)
  To: Paul A Jungwirth <[email protected]>; +Cc: Kirill Reshke <[email protected]>; jian he <[email protected]>; PostgreSQL Hackers <[email protected]>

Paul A Jungwirth <[email protected]> writes:
> Thanks for taking a look! I changed places we say `-- not unique` to
> now say `-- overlaps`. v5 is attached to my reply to jian he.

I pushed this patch, but not without significant revisions:

* I didn't think the case had been made for adding this field to
typcache.  There's only one user, it's in a very minority-interest
code path, and it'd add both space in TypeCacheEntry and a
noticeable number of cycles to TYPECACHE_DOMAIN_BASE_INFO requests.
(Admittedly the extra-cycles complaint is weak, since we expect
TypeCacheEntries to live a long time once populated.)  The other
problem was that the patch was buggy anyway: there is more than
one place in typcache.c that'd have to change.  I just moved the
get_typtype(typcache->domainBaseType) call to runtime instead.
We can revisit that if more potential uses appear.

* I thought the number of new regression test cases was quite
excessive compared to the value.  On my machine, the runtime for
without_overlaps.sql went from ~420ms to ~500ms, and it's already
the slowest in its parallel group by a good margin.  I cut it back to
just testing the base domain-over-range and range-over-domain cases,
which added only ~5ms.  (To be clear, I think it was worth making
those tests just to verify there are no other holes.  But carrying
them forward forevermore is another matter.)

* I happened to notice that the loop just above the changes in
transformIndexConstraint was buggy: it had "break" where it
should have had "continue".  That bug can be demonstrated with

CREATE TABLE temporal_rng4 (
  id int4range,
  bogus int,
  valid_at int4
);

ALTER TABLE temporal_rng4 DROP COLUMN bogus;

ALTER TABLE temporal_rng4 ADD
  CONSTRAINT temporal_rng4_pk UNIQUE (id, valid_at WITHOUT OVERLAPS);

which gave different errors with and without the DROP COLUMN
step.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

end of thread, other threads:[~2026-04-07 18:57 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-12-03 07:38 domain for WITHOUT OVERLAPS jian he <[email protected]>
2025-12-03 18:39 ` Paul A Jungwirth <[email protected]>
2025-12-11 04:23   ` jian he <[email protected]>
2025-12-23 03:25     ` Paul A Jungwirth <[email protected]>
2025-12-23 18:08       ` Paul A Jungwirth <[email protected]>
2025-12-29 07:17       ` jian he <[email protected]>
2026-03-10 07:41         ` jian he <[email protected]>
2026-03-10 16:31           ` Paul A Jungwirth <[email protected]>
2026-03-10 09:53         ` Kirill Reshke <[email protected]>
2026-03-10 16:33           ` Paul A Jungwirth <[email protected]>
2026-04-07 18:57             ` Tom Lane <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox