Thread: should all not-null constraints be inherited?

Hello,

This is how one can create a parent with a non-nullable column and a 
child with the same column nullable:

create table t1(a int not null);
create table t2() inherits (t1);
alter table t2 alter column a drop not null;
insert into t2 select null;

pg_dump produces a dump that fails to restore, as the change by the 
alter command doesn't get addressed.
Reproduced in 10 and 14devel.

Should we fix ALTER TABLE or pg_dump?

Best, Alex

Alexey Bashtanov <bashtanov@imap.cc> writes:
> This is how one can create a parent with a non-nullable column and a 
> child with the same column nullable:

Yeah, that's been on the to-do list for a long time.  The current
theory about it is that NOT NULL constraints ought to be represented
as entries in pg_constraint like CHECKs are, so that the inheritance
count and so forth could be managed the same way.  We could keep
attnotnull for convenience but it'd just mirror the existence of
the pg_constraint entry.

IIRC Alvaro had a WIP patch for this, but I've not heard anything
about it lately.

            regards, tom lane

On 2021-Mar-31, Tom Lane wrote:

> Yeah, that's been on the to-do list for a long time.  The current
> theory about it is that NOT NULL constraints ought to be represented
> as entries in pg_constraint like CHECKs are, so that the inheritance
> count and so forth could be managed the same way.  We could keep
> attnotnull for convenience but it'd just mirror the existence of
> the pg_constraint entry.
> 
> IIRC Alvaro had a WIP patch for this, but I've not heard anything
> about it lately.

That one is the first thing on my list for the next cycle.

-- 
Álvaro Herrera       Valdivia, Chile