Thread

Re: domain for WITHOUT OVERLAPS

Paul A Jungwirth <pj@illuminatedcomputing.com> — 2025-12-23T18:08:19Z

On Mon, Dec 22, 2025 at 7:25 PM Paul A Jungwirth
<pj@illuminatedcomputing.com> wrote:
>
> On Wed, Dec 10, 2025 at 8:23 PM jian he <jian.universality@gmail.com> 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              ~{:-)
pj@illuminatedcomputing.com