Postgres 17 domains with NOT NULL and pg_get_constraintdef()
shammat@gmx.net
From: shammat@gmx.net
To: pgsql-general@lists.postgresql.org
Date: 2025-01-08T11:15:19Z
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.
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Add pg_constraint rows for not-null constraints
- 14e87ffa5c54 18.0 cited