Re: information_schema and not-null constraints

On Tue, Sep 5, 2023 at 2:50 PM Vik Fearing <vik(at)postgresfriends(dot)org> wrote:

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

I'm presuming that the view constraint_column_usage [1] is an integral part
of all this though I haven't taken the time to figure out exactly how we
are implementing it today.

I'm not all that for either A or B since the status quo seems workable.
Though ideally if the system has unique names per schema then everything
should just work - having the views produce duplicated information (as
opposed to nothing) if they are used when the DBA doesn't enforce the
standard's requirements seems plausible.

David J.

[1]
https://www.postgresql.org/docs/current/infoschema-constraint-column-usage.html