Re: information_schema.check_constraints Inconsistencies
| От | Hristo Ivanov |
|---|---|
| Тема | Re: information_schema.check_constraints Inconsistencies |
| Дата | |
| Msg-id | CAAH_5C-MP2ZEGH=qwzSM4bDvkya1YhF4tY3w5h1QqiZGiywJMQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: information_schema.check_constraints Inconsistencies (Andrew Gierth <andrew@tao11.riddles.org.uk>) |
| Ответы |
Re: information_schema.check_constraints Inconsistencies
|
| Список | pgsql-bugs |
Hello guys,
Thanks for your fast replies.
2018-09-18 22:59 GMT+03:00 Andrew Gierth <andrew@tao11.riddles.org.uk>:
This completely disregards the information_schema objects.
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_
and a.schemaname = 'events'
and c.contype = '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_
and a.schemaname = 'events'
and c.contype = 'c'
Looking forward to hearing from you.
Best regards,
Hristo Ivanov
В списке pgsql-bugs по дате отправления: