Re: cataloguing NOT NULL constraints

Hello Alexander

On 2024-May-02, Alexander Lakhin wrote:

> Could you also clarify, please, how CREATE TABLE ... LIKE is expected to
> work with NOT NULL constraints?

It should behave identically to 16. If in 16 you end up with a
not-nullable column, then in 17 you should get a not-null constraint.

> I wonder whether EXCLUDING CONSTRAINTS (ALL) should cover not-null
> constraints too. What I'm seeing now, is that:
> CREATE TABLE t1 (i int, CONSTRAINT nn NOT NULL i);
> CREATE TABLE t2 (LIKE t1 EXCLUDING ALL);
> \d+ t2
> -- ends with:
> Not-null constraints:
>     "nn" NOT NULL "i"

In 16, this results in
Table "public.t2"
Column │ Type │ Collation │ Nullable │ Default │ Storage │ Compression │ Stats target │ Description
────────┼─────────┼───────────┼──────────┼─────────┼─────────┼─────────────┼──────────────┼─────────────
i │ integer │ │ not null │ │ plain │ │ │
Access method: heap

so the fact that we have a not-null constraint in pg17 is correct.

> Or a similar case with PRIMARY KEY:
> CREATE TABLE t1 (i int PRIMARY KEY);
> CREATE TABLE t2 (LIKE t1 EXCLUDING CONSTRAINTS EXCLUDING INDEXES);
> \d+ t2
> -- leaves:
> Not-null constraints:
>     "t2_i_not_null" NOT NULL "i"

Here you also end up with a not-nullable column in 16, so I made it do
that.

Now you could argue that EXCLUDING CONSTRAINTS is explicit in saying
that we don't want the constraints; but in that case why did 16 mark the
columns as not-null? The answer seems to be that the standard requires
this. Look at 11.3 <table definition> syntax rule 9) b) iii) 4):

4) If the nullability characteristic included in LCDi is known not
nullable, then let LNCi be NOT NULL; otherwise, let LNCi be the
zero-length character string.

where LCDi is "1) Let LCDi be the column descriptor of the i-th column
of LT." and then

5) Let CDi be the <column definition>
LCNi LDTi LNCi

Now, you could claim that the standard doesn't mention
INCLUDING/EXCLUDING CONSTRAINTS, therefore since we have come up with
its definition then we should make it affect not-null constraints.
However, there's also this note:

NOTE 520 — <column constraint>s, except for NOT NULL, are not included in
CDi; <column constraint definition>s are effectively transformed to <table
constraint definition>s and are thereby also excluded.

which is explicitly saying that not-null constraints are treated
differently; in essence, with INCLUDING CONSTRAINTS we choose to affect
the constraints that the standard says to ignore.

Thanks for looking!

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"Learn about compilers. Then everything looks like either a compiler or
a database, and now you have two problems but one of them is fun."
https://twitter.com/thingskatedid/status/1456027786158776329