Re: information_schema and not-null constraints

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.