Re: Function pg_get_constraintdef

Here is a followup. The problem occurs if the database has a domain with a check constraint. The problem does not exist in PostgreSQL 15.9

***************
PostgreSQL 17.1
***************

Empty database

SELECT pg_get_constraintdef(c.oid)
FROM pg_catalog.pg_constraint c;
--No error

CREATE TABLE A (a_id SMALLINT NOT NULL,
CONSTRAINT pk_a PRIMARY KEY (a_id),
CONSTRAINT chk_a_id CHECK (a_id>0));

SELECT pg_get_constraintdef(c.oid)
FROM pg_catalog.pg_constraint c;
--No error

CREATE DOMAIN d_a SMALLINT NOT NULL
CONSTRAINT chk_d_a CHECK (VALUE>=0);

SELECT pg_get_constraintdef(c.oid)
FROM pg_catalog.pg_constraint c;
--ERROR: invalid constraint type "n"

***************
PostgreSQL 15.9
***************

Empty database

SELECT pg_get_constraintdef(c.oid)
FROM pg_catalog.pg_constraint c;
--No error

CREATE TABLE A (a_id SMALLINT NOT NULL,
CONSTRAINT pk_a PRIMARY KEY (a_id),
CONSTRAINT chk_a_id CHECK (a_id>0));

SELECT pg_get_constraintdef(c.oid)
FROM pg_catalog.pg_constraint c;
--No error

CREATE DOMAIN d_a SMALLINT NOT NULL
CONSTRAINT chk_d_a CHECK (VALUE>=0);

SELECT pg_get_constraintdef(c.oid)
FROM pg_catalog.pg_constraint c;
--No error

Best regards
Erki Eessaar

________________________________
From: Erki Eessaar <erki(dot)eessaar(at)taltech(dot)ee>
Sent: Thursday, November 21, 2024 13:46
To: pgsql-bugs(at)lists(dot)postgresql(dot)org <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Function pg_get_constraintdef

Hello

SELECT pg_get_constraintdef(c.oid)
FROM pg_catalog.pg_constraint c;

If I execute the query in two different servers with PostgreSQL 17.1 and in different databases in these servers, then I get the answer

ERROR: invalid constraint type "n"

In case of an empty database I do not get the error. The query worked in the servers before upgrading to PostgreSQL 17.

If I execute the query in a database of a server with PostgreSQL 15, then I do not get the error.

Best regards
Erki Eessaar