Re: not null constraints, again

On 2024-Sep-24, jian he wrote:

> sql-createtable.html
> SECTION: LIKE source_table [ like_option ... ]
> INCLUDING CONSTRAINTS
> CHECK constraints will be copied. No distinction is made between
> column constraints and table constraints. Not-null constraints are
> always copied to the new table.
>
> drop table if exists t, t_1,ssa;
> create table t(a int, b int, not null a no inherit);
> create table ssa (like t INCLUDING all);
>
> Here create table like won't include no inherit not-null constraint,
> seems to conflict with the doc?

Hmm, actually I think this is a bug, because if you have CHECK
constraint with NO INHERIT, it will be copied:

create table t (a int check (a > 0) no inherit);
create table ssa (like t including constraints);

55490 18devel 141626=# \d+ ssa
Tabla «public.ssa»
Columna │ Tipo │ Ordenamiento │ Nulable │ Por omisión │ Almacenamiento>
─────────┼─────────┼──────────────┼─────────┼─────────────┼───────────────>
a │ integer │ │ │ │ plain >
Restricciones CHECK:
"t_a_check" CHECK (a > 0) NO INHERIT
Método de acceso: heap

It seems that NOT NULL constraint should behave the same as CHECK
constraints in this regard, i.e., we should not heed NO INHERIT in this
case.

I have made these changes and added some tests, and will be posting a v5
shortly.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
<inflex> really, I see PHP as like a strange amalgamation of C, Perl, Shell
<crab> inflex: you know that "amalgam" means "mixture with mercury",
more or less, right?
<crab> i.e., "deadly poison"