Re: cataloguing NOT NULL constraints - Mailing list pgsql-hackers

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