Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMgsg-000000029Tv-3TrH for pgsql-bugs@arkaria.postgresql.org; Fri, 30 Oct 2026 07:18:31 +0000 Received: from localhost ([127.0.0.1]:51180 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xMgsf-00000008qJl-1sDL for pgsql-bugs@arkaria.postgresql.org; Fri, 30 Oct 2026 07:18:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:51782) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMJVc-00000002tZC-21Pg for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 06:21:08 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMJVZ-00000001Xg7-3zoo for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 06:21:08 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=qJzcVtssWrT39A5kFknHrTtOHlXIyuEUErdTw1A3nDc=; b=efd4ZWcLUVg56FcoKNQSwHFCg+ NTsJBsaWj5xfq+3aSl8hL8dyWgwwUwSrIngmg+e+24q8aZbIOF6Q48GqKVAxxy845lvlZVIkyS+Bc 85Z7Bo8AUgxZ1x/bXMW9byNZGOtjVVOlwmEip6e1oTf3hIQoCGZEd1+1JXRNBhYN1h8DL+4fltAyb uSKughDqzBDJaU/C1RfWaMR+3AfT8sdz0FwkGGCfUQ6oYYMSZvS2iBx0T2KEXNzzQY5Lf+GVGl1C1 106Jkp6hyYnTetTYCHkH3jKErA2jFopo5yq5Npeu4mtdOo0a+32fTW8a93f+NjxSflEFFmaCUM6zr 1AvY6zvA==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMJVY-00000004HK3-3BE3 for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 06:21:04 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xMJVW-00000005b0p-3F4c for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 06:21:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19808: NOT NULL NO INHERIT copied by CREATE TABLE (LIKE) not enforced on partitioned table To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: 303677365@qq.com Reply-To: 303677365@qq.com, pgsql-bugs@lists.postgresql.org Date: Thu, 29 Oct 2026 06:20:55 +0000 Message-ID: <19808-6fce48c9105a1ca0@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk The following bug has been logged on the website: Bug reference: 19808 Logged by: chunling qin Email address: 303677365@qq.com PostgreSQL version: 18.6 Operating system: Linux x86_64 Description: =20 When CREATE TABLE (LIKE) copies a column declared NOT NULL NO INHERIT into a *partitioned* table, the NOT NULL flag is recorded on the parent (pg_attribute.attnotnull =3D true) but is not propagated to the partitions,= so an INSERT of NULL succeeds. Because the parent column is marked attnotnull, the planner also prunes "WHERE a IS NULL" to an empty result, making the stored NULL rows invisible to that predicate while "a + 0 IS NULL" still finds them - two logically equivalent predicates disagree. Test case (self-contained): CREATE TABLE nn_src (a int NOT NULL NO INHERIT, b text); CREATE TABLE nn_p (LIKE nn_src) PARTITION BY LIST (a); CREATE TABLE nn_pd PARTITION OF nn_p DEFAULT; INSERT INTO nn_p VALUES (NULL, 'null-row'); -- accepted Catalog state: the parent has attnotnull =3D true for column a, the partiti= on has attnotnull =3D false. Result on 18.6 (official docker image postgres:latest): SELECT a, b FROM nn_p; -> (NULL, 'null-row') SELECT count(*) FROM nn_p WHERE a IS NULL; -> 0 SELECT count(*) FROM nn_p WHERE a + 0 IS NULL; -> 1 EXPLAIN (COSTS OFF) SELECT * FROM nn_p WHERE a IS NULL; Result One-Time Filter: false Baselines - the same INSERT is rejected whenever NO INHERIT is not involved: (1) LIKE into a non-partitioned table: CREATE TABLE nn_plain (LIKE nn_src); INSERT INTO nn_plain VALUES (NULL,'x'); -> ERROR: null value in column "a" of relation "nn_plain" violates not-null constraint (2) plain NOT NULL (no NO INHERIT) declared in the partitioned parent: CREATE TABLE nn_p2 (a int NOT NULL, b text) PARTITION BY LIST (a); ... INSERT NULL -> ERROR: null value in column "a" of relation "nn_p2d" violates not-null constraint (3) LIKE copying a plain NOT NULL source column into a partitioned table: CREATE TABLE src2 (a int NOT NULL, b text); CREATE TABLE nn_p3 (LIKE src2) PARTITION BY LIST (a); ... INSERT NULL -> ERROR: null value in column "a" of relation "nn_p3d" violates not-null constraint So NO INHERIT is the specific trigger: the parent gets attnotnull =3D true without enforcement or propagation to partitions, which is contradictory for a partitioned table - inserts are routed to the partition and only checked there, while the planner uses the parent's flag to prove that no NULLs can exist. Expected: either the NO INHERIT qualifier is rejected/ignored for a partitioned table created via LIKE, or the constraint is enforced like every other path. In any case "a IS NULL" and "a + 0 IS NULL" must not disagree.