Feedback-ID: ia2694551:Fastmail
Date: Tue, 24 Sep 2024 18:59:46 +0200
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: jian he <jian.universality@gmail.com>
Cc: Tender Wang <tndrwang@gmail.com>, 
	Pg Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: not null constraints, again
Message-ID: <202409241659.p77vki2o6wbp@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <CACJufxHNQs3PyUNz9h6tThFyrXBuNzxb2G=2n4DMZNPmKVKB_Q@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/202409241659.p77vki2o6wbp%40alvherre.pgsql>
Precedence: bulk

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"