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.
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
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.