Re: information_schema and not-null constraints

On 9/5/23 19:15, Alvaro Herrera wrote:
> On 2023-Sep-05, Alvaro Herrera wrote:
> 
> Looking now at what to do for CHECK_CONSTRAINTS with domain constraints,
> I admit I'm completely confused about what this view is supposed to
> show.  Currently, we show the constraint name and a definition like
> "CHECK (column IS NOT NULL)".  But since the table name is not given, it
> is not possible to know to what table the column name refers to.  For
> domains, we could show "CHECK (VALUE IS NOT NULL)" but again with no
> indication of what domain it applies to, or anything at all that would
> make this useful in any way whatsoever.

Constraint names are supposed to be unique per schema[1] so the view 
contains the minimum required information to identify the constraint.

> So this whole thing seems pretty futile and I'm disinclined to waste
> much time on it.

Until PostgreSQL either
   A) obeys the spec on this uniqueness, or
   B) decides to deviate from the information_schema spec;
this view will be completely useless for actually getting any useful 
information.

I would like to see us do A because it is the right thing to do.  Our 
autogenerated names obey this rule, but who knows how many duplicate 
names per schema are out there in the wild from people specifying their 
own names.

I don't know what the project would think about doing B.


[1] SQL:2023-2 11.4  Syntax Rule 4
-- 
Vik Fearing