Re: cataloguing NOT NULL constraints

On Wed, 2022-08-17 at 20:12 +0200, Alvaro Herrera wrote:
> I've been working on having NOT NULL constraints have pg_constraint
> rows.
>
> Everything is working now.  Some things are a bit weird, and I would
> like opinions on them:
>
> 1. In my implementation, you can have more than one NOT NULL
>    pg_constraint row for a column.  What should happen if the user does
>    ALTER TABLE .. ALTER COLUMN .. DROP NOT NULL;
>    ?  Currently it throws an error about the ambiguity (ie. which
>    constraint to drop).

I'd say that is a good solution, particularly if there is a hint to drop
the constraint instead, similar to when you try to drop an index that
implements a constraint.

>    Using ALTER TABLE DROP CONSTRAINT works fine, and the 'attnotnull'
>    bit is lost when the last one such constraint goes away.

Wouldn't it be the correct solution to set "attnotnumm" to FALSE only
when the last NOT NULL constraint is dropped?

> 2. If a table has a primary key, and a table is created that inherits
>    from it, then the child has its column(s) marked attnotnull but there
>    is no pg_constraint row for that.  This is not okay.  But what should
>    happen?
>
>    1. a CHECK(col IS NOT NULL) constraint is created for each column
>    2. a PRIMARY KEY () constraint is created

I think it would be best to create a primary key constraint on the
partition.

Yours,
Laurenz Albe