Postgres 17 domains with NOT NULL and pg_get_constraintdef()

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.

Responses

Browse pgsql-general by date

  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?