From: | shammat(at)gmx(dot)net |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Postgres 17 domains with NOT NULL and pg_get_constraintdef() |
Date: | 2025-01-08 11:15:19 |
Message-ID: | f90c6099-521e-48d7-99eb-07e85f606be4@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I am not sure if this is intended behavior or an oversight/bug.
Assume the following domain definition:
CREATE DOMAIN sample_domain AS numeric(12,2) NOT NULL constraint positive_value CHECK (value > 0);
Up until Postgres 16 it was possible to retrieve the domain constraints using:
select t.typname,
pg_catalog.pg_get_constraintdef(ci.oid, true)
from pg_catalog.pg_type t
join pg_catalog.pg_constraint ci on ci.contypid = t.oid
where t.typtype = 'd'
and t.typname = 'sample_domain'
However, because the domain is defined with NOT NULL, this fails with
> ERROR: invalid constraint type "n"
with 17.2 on Windows and Linux.
This is a result of storing the NOT NULL constraint in pg_constraint
and can easily be avoided by either using contype <> 'n' or by not calling
pg_get_constraintdef if contype = 'n'
Is this a bug/oversight in pg_get_constraintdef() or does it just not support NOT NULL
constraints? The manual does not show any restriction on the usage.
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2025-01-08 13:10:45 | Re: Postgres 17 domains with NOT NULL and pg_get_constraintdef() |
Previous Message | Tom Lane | 2025-01-08 06:53:34 | Re: Postgres do not support tinyint? |