Re: information_schema.check_constraints Inconsistencies

Hello guys,

Thanks for your fast replies.

2018-09-18 22:59 GMT+03:00 Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>:

>
> In the SQL spec, the name of a constraint is not local to the table; in
> a given schema, the name must be unique. PostgreSQL does not enforce
> this, and generally treats constraint names as local to a single
> _table_; this is difficult to fix retroactively because it would make
> old databases fail to restore if the spec's conditions were enforced.
>
> https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL_
> Standard#Constraint_name_scope
>
I didn't know that constraint names had to be unique. Even if that is true,
I don't think returning wrong constraints in this case (belonging to a
different table) is the right thing to do. This means that PostgreSQL is
conforming to the standard in only places, while the dependencies are
clearly not standard compliant. Since the likelihood of fixing dependencies
is fairly small, I would suggest fixing the constraints selection behavior.

Hristo> (2) Second, it also lists NOT NULL constraints, even though
> Hristo> they are not created as check constraints.
>
> This is required by the SQL spec, which treats NOT NULL as merely a
> syntactic shorthand for CHECK(colname IS NOT NULL). But see also
>
> https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL_
> Standard#NOT_NULL_constraints_on_composite-type_columns
>
> which implies that for composite-type columns, NOT NULL and
> CHECK(colname IS NOT NULL) actually have different semantics in PG.
>
Fair enough. Could I suggest having a column to discriminate non-null
constraints from the rest? Like, named "not_null", to be either "yes" or
"no", or, better, simple Boolean?

FYI, the only solution I found to this problem, is:
select c.*
from pg_class t
join pg_tables a
on t.relname = a.tablename
join pg_constraint c
on c.conrelid = t.oid
where a.tablename = 'horse_racing_purchase_event'
and a.schemaname = 'events'
and c.contype = 'c'
This completely disregards the information_schema objects.

Looking forward to hearing from you.

Best regards,
Hristo Ivanov