public inbox for [email protected]  

help / color / mirror / Atom feed

From: [email protected]
To: [email protected]
Subject: Postgres 17 domains with NOT NULL and pg_get_constraintdef()
Date: Wed, 8 Jan 2025 12:15:19 +0100
Message-ID: <[email protected]> (raw)

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.

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Postgres 17 domains with NOT NULL and pg_get_constraintdef()
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox