public inbox for [email protected]
help / color / mirror / Atom feedFrom: Paul A Jungwirth <[email protected]>
To: jian he <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: domain for WITHOUT OVERLAPS
Date: Wed, 11 Feb 2026 10:50:49 -0800
Message-ID: <CA+renyX_5VweE4OJp+wYy2ON-TNr8rGuxt25GkacuWt0ZPP37Q@mail.gmail.com> (raw)
In-Reply-To: <CACJufxFkUPuh+wXGgVFfGeU+HGuCnDJxWmg-8QwVtFj=EnxTGw@mail.gmail.com>
References: <CACJufxGoAmN_0iJ=hjTG0vGpOSOyy-vYyfE+-q0AWxrq2_p5XQ@mail.gmail.com>
<CA+renyU=ukksgiMQioaUixG+DJY1w1cwGdssKy9_hj_uaYDkBA@mail.gmail.com>
<CACJufxEABtUZf=M9h3XpvjiWKFNDxObPpe6Ly9_dd-k3_xJJ+w@mail.gmail.com>
<CA+renyW+ia6Uewa5+9f+cgTinndj8wt7yiG=3yZ_FG-hmiHn3w@mail.gmail.com>
<CACJufxFkUPuh+wXGgVFfGeU+HGuCnDJxWmg-8QwVtFj=EnxTGw@mail.gmail.com>
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]
Attachments:
[application/octet-stream] v4-0001-Add-support-for-domain-types-in-WITHOUT-OVERLAPS.patch (21.8K, 2-v4-0001-Add-support-for-domain-types-in-WITHOUT-OVERLAPS.patch)
download | inline diff:
From 7d0504a4064df582b941aa253912306f17114ffb Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 29 Dec 2025 15:15:23 +0800
Subject: [PATCH v4] 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 | 178 +++++++++++++++++-
src/test/regress/sql/without_overlaps.sql | 152 ++++++++++++++-
6 files changed, 341 insertions(+), 8 deletions(-)
diff --git a/src/backend/executor/execIndexing.c b/src/backend/executor/execIndexing.c
index 6ae0f959592..d3e5ed92d1e 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 b5f4c72459d..8d5f9a78525 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -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 dc4b1a56414..5f4c8b90224 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 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..0e18e9dfb44 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)'); -- not unique
+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)'); -- not unique
+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)'); -- not unique
+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)'); -- not unique
+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)}'); -- not unique
+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)}'); -- not unique
+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)}'); -- not unique
+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)}'); -- not unique
+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..c30cef1cf2d 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)'); -- not unique
+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)'); -- not unique
+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)'); -- not unique
+INSERT INTO temporal_rng4 VALUES ('[2,3)', '[B,C)'), ('[2,3)', '[A,C)'); -- not unique
+
+-- 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)}'); -- not unique
+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)}'); -- not unique
+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)}'); -- not unique
+INSERT INTO temporal_mltrng4 VALUES ('[2,3)', '{[B,C)}'), ('[2,3)', '{[A,C)}'); -- not unique
+
+-- 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
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: domain for WITHOUT OVERLAPS
In-Reply-To: <CA+renyX_5VweE4OJp+wYy2ON-TNr8rGuxt25GkacuWt0ZPP37Q@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox