From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Alexander Lakhin <exclusion@gmail.com>
Cc: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Andrew Bille <andrewbille@gmail.com>
Cc: Peter Eisentraut <peter@eisentraut.org>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: cataloguing NOT NULL constraints
Date: Thu, 2 May 2024 18:21:56 +0200
Message-ID: <202405021621.ftmuhmfg7f2j@alvherre.pgsql> (raw)
In-Reply-To: <83bfef0d-3e3c-e4c7-f6c4-29db5e632883@gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: alvherre@alvh.no-ip.org, exclusion@gmail.com, dean.a.rasheed@gmail.com, andrewbille@gmail.com, peter@eisentraut.org, robertmhaas@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: cataloguing NOT NULL constraints
In-Reply-To: <202405021621.ftmuhmfg7f2j@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox