Message-ID: <f90c6099-521e-48d7-99eb-07e85f606be4@gmx.net>
Date: Wed, 8 Jan 2025 12:15:19 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Content-Language: de-DE, en-US
To: pgsql-general@lists.postgresql.org
From: shammat@gmx.net
Subject: Postgres 17 domains with NOT NULL and pg_get_constraintdef()
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
UI-OutboundReport: notjunk:1;M01:P0:Js72o2vpTbk=;BgQmIWIMEkTnPAEdeNH44LTr4of
 c0GniMCzRyqKZVtOKwVzHjYJJuugMxbsVSEJbo4FuEmCWGj1IB5DfBORrA1I0LNCccveSBYH5
 6RTchTL2k/uPnXiOP8JWauT6cq0jHti3ixievn6COhlTfLSe6eSy4xO6P0JPrkgHMYKuPUdJX
 JP+jo+gv8+kdesglcMSJQL9Ip2vcEuFkGBsuph5YHn9Z6ZpTcRrmabFRFGOagbmCtnmgi3Ryq
 1auP8P1P2TSXMIzJLIIpDSfLj5TV8FUjybEbgCFfDjLAMIukqUyKcJ7/A5Gp7rG8EIQT3190z
 0YjnR3mdgQeLr5w8FVOHOX1vpIX0FJ4Gk85sTtZsdq7fQ5DHXOXFEWTmG6J6CFVEXrnaJamvN
 DouqTs9N2lpRhe73JNE1E5uvYUPDqVMlESLc8vjjnsLCC/PKeKIR/287sUr17Uc5w5mJYqAgB
 toy/LXLHY/AWKZTC8G3CjKdWVgaCW9E3Er+To03DEI8kvrwE8Wb1+jhjqh1lc6EqX/DvkMqIr
 9DcBs/H3jxZFagfQWFXKHAJe3Z6Z6gRXkQEydYArfUQbgNKPiMe0+zYGobWw45AkHxm+WV7au
 Wz7/D3dnXP8nN3q/eWpAEi+Teph63mQNSU+ISAg6ZwgLotMPUuSLM4eYmHO03Hh8bmqUPb85o
 k8XROhyulF6Y7lVcFE1moqPjBdGva/8gcojcZMeSw67NdTVW32HwDiuPZFeJUraqKvuB+/DaZ
 qWfe5vYVj70KcHdczY0sQfM9CJ8XB1p0uNQC38ivOWC3nXFwofpuLZ2Gg4o6ncVcdFaveRlWE
 hcY6hSWHlRsVi2IRz9nIcYeLyoPdNIzTu2n2KpCHWds6QakGmpwgIM8/Q4eGMX0Tqu68uexcW
 ybrIe3ZCkDOMFUM/q0vVyR5VRqXoAs2N9p1ZttM5Oq4CtBv5JQN5ym+7lZELLtxzFMO3igafF
 w0Qq6lAfgQC+Kqno14RjJ6mUUgUDOAkrcatLZMtmhxlKUskFGd5y6eT4Z/Q006qLeSomY9TgX
 yGnR7XnvOpxXnMo2v27Cpfn3zQHZm84O4yvuv/k1p3ijyqmRUoHUv6b2qjKiZ+jGZqyCvzRgA
 LSxw4Too0vhGVNn+QgeQDirJL9y5za
Archived-At: <https://www.postgresql.org/message-id/f90c6099-521e-48d7-99eb-07e85f606be4%40gmx.net>
Precedence: bulk

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 posit=
ive_value CHECK (value > 0);

Up until Postgres 16 it was possible to retrieve the domain constraints us=
ing:

    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 =3D t.oid
    where t.typtype =3D 'd'
      and t.typname =3D '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 =3D 'n'

Is this a bug/oversight in pg_get_constraintdef() or does it just not supp=
ort NOT NULL
constraints? The manual does not show any restriction on the usage.