Re: information_schema.check_constraints Inconsistencies

>>>>> "Hristo" == Hristo Ivanov <hristo(dot)atanassov(at)gmail(dot)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)