Re: information_schema and not-null constraints

From: Peter Eisentraut <peter(at)eisentraut(dot)org>
To: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: information_schema and not-null constraints
Date: 2023-09-19 07:01:56
Message-ID: 799b59ef-3330-f0d2-ee23-8cdfa1740987@eisentraut.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On 14.09.23 10:20, Peter Eisentraut wrote:
> On 06.09.23 19:52, Alvaro Herrera wrote:
>> +    SELECT current_database()::information_schema.sql_identifier AS
>> constraint_catalog,
>> +           rs.nspname::information_schema.sql_identifier AS
>> constraint_schema,
>> +           con.conname::information_schema.sql_identifier AS
>> constraint_name,
>> +           format('CHECK (%s IS NOT NULL)',
>> at.attname)::information_schema.character_data AS check_clause
>
> Small correction here: This should be
>
> pg_catalog.format('%s IS NOT NULL',
> at.attname)::information_schema.character_data AS check_clause
>
> That is, the word "CHECK" and the parentheses should not be part of the
> produced value.

Slightly related, so let's just tack it on here:

While testing this, I noticed that the way the check_clause of regular
check constraints is computed appears to be suboptimal. It currently does

CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data)

which ends up with an extra set of parentheses, which is ignorable, but
it also leaves in suffixes like "NOT VALID", which don't belong into
that column. Earlier in this thread I had contemplated a fix for the
first issue, but that wouldn't address the second issue. I think we can
fix this quite simply by using pg_get_expr() instead. I don't know why
it wasn't done like that to begin with, maybe it was just a (my?)
mistake. See attached patch.

Attachment Content-Type Size
0001-Simplify-information-schema-check-constraint-deparsi.patch text/plain 1.6 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2023-09-19 07:20:36 Re: Better help output for pgbench -I init_steps
Previous Message Ryoga Yoshida 2023-09-19 06:29:11 Re: Bug fix for psql's meta-command \ev