>>>>> "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)