Re: information_schema.check_constraints Inconsistencies

>>>>> "Hristo" == Hristo Ivanov <hristo.atanassov@gmail.com> writes:

 Hristo> Hello,
 Hristo> I am writing with regards to some wrong results I keep on
 Hristo> receiving when using check_constraints view from
 Hristo> information_schema:

 Hristo> (1) First, it shows constraints grouped by name, regardless of
 Hristo> the relation used: when having two constraints with the same
 Hristo> name in different tables, it shows both in both tables,
 Hristo> regardless of their belonging;

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

 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.

-- 
Andrew (irc:RhodiumToad)