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 1xIq5z-00000003vRC-38xP for pgsql-bugs@arkaria.postgresql.org; Mon, 19 Oct 2026 16:20:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xIq5y-00000007waY-3BAc for pgsql-bugs@arkaria.postgresql.org; Mon, 19 Oct 2026 16:20:18 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xIROx-000000036GQ-35tB for pgsql-bugs@lists.postgresql.org; Sun, 18 Oct 2026 13:58:15 +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 1xIROs-00000002oju-1DHW for pgsql-bugs@lists.postgresql.org; Sun, 18 Oct 2026 13:58:15 +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=AU+FUqUp7YK5gz40R3H6AryOR9EInaefa7r2PHfF7dk=; b=YnuyUy0gPJ9HtJUsD+Lr2GLsda 9yf4rr5TWj3DrSevkLKxsSzCbFXNrBG268t8FUgvtVbP3kBibH7eEaCdyijeH83YPG1Z+1aJbUfOw V6qe9/qSzPeWVHrJCq0eiK4tD7gO2JRAXvxHj4g5lr7n/JPuiBcAKGrVcjszgwELIGDVYAkMqSzDH xelHusjMM43L4CDf22P4w8O8D8w7QVhYivanL4AFxmSNOHGji91v79LtAqdx3tBFYvsc4RPP6KZFi J+rzt2UpFmf4ceU8WeGTkTwMKWZ5Gw0v842Lq+OoyU/Wp6kw/uaQI3m2+j7smked0aG4ZT9XhMQer rjDICFrQ==; 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 1xIROn-00000007i0C-0goN for pgsql-bugs@lists.postgresql.org; Sun, 18 Oct 2026 13:58:07 +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 1xIROl-00000005mQP-2KJ0 for pgsql-bugs@lists.postgresql.org; Sun, 18 Oct 2026 13:58:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19770: CREATE TABLE (LIKE ...) PARTITION BY copies a NO INHERIT not-null constraint; NULLs then go into par To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: anncalla@163.com Reply-To: anncalla@163.com, pgsql-bugs@lists.postgresql.org Date: Sun, 18 Oct 2026 13:58:03 +0000 Message-ID: <19770-7c6e75fc30df7e06@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: 19770 Logged by: Ann C Email address: anncalla@163.com PostgreSQL version: 18.6 Operating system: Ubuntu 24.04 x86_64 Description: =20 A not-null constraint marked NO INHERIT is rejected on a partitioned table when it is declared directly: ERROR: not-null constraints on partitioned tables cannot be NO INHERIT But CREATE TABLE pp (LIKE p) PARTITION BY ... copies such a constraint from p without an error. pg_constraint then has the constraint on pp with connoinherit =3D true. The partitions do not get a not-null constraint, so INSERT INTO pp VALUES (NULL) succeeds and the row is stored in the default partition. Queries on the parent are planned with c0 known to be NOT NULL, so "WHERE c0 IS NULL" becomes a constant false and returns 0 rows, although SELECT count(*) FROM pp returns the NULL row and the partition itself finds it. Expected: CREATE TABLE ... (LIKE p) PARTITION BY should fail with the same error as the direct declaration (or copy the constraint without NO INHERIT). Reproduced on 18.6 and 19beta4 (official Docker images). The ALTER CONSTRAINT ... NO INHERIT path to the same state was fixed on 2026-05-22 ("Prevent setting NO INHERIT on partitioned NOT NULL constraints"); this is another path. CREATE TABLE p (c0 int, CONSTRAINT p_nn NOT NULL c0 NO INHERIT); CREATE TABLE q (c0 int NOT NULL NO INHERIT) PARTITION BY RANGE (c0); -- ERROR: not-null constraints on partitioned tables cannot be NO INHERIT CREATE TABLE pp (LIKE p) PARTITION BY RANGE (c0); -- CREATE TABLE (no error) SELECT conname, conrelid::regclass, contype, connoinherit FROM pg_constraint WHERE conrelid =3D 'pp'::regclass; -- p_nn | pp | n | t CREATE TABLE pp_def PARTITION OF pp DEFAULT; -- column c0 is nullable in pp_def INSERT INTO pp VALUES (NULL); -- INSERT 0 1 SELECT count(*) FROM pp; -- 1 SELECT count(*) FROM pp WHERE c0 IS NULL; -- 0, expected 1 SELECT count(*) FROM pp_def WHERE c0 IS NULL; -- 1 EXPLAIN (COSTS OFF) SELECT count(*) FROM pp WHERE c0 IS NULL; -- Aggregate -- -> Result -- One-Time Filter: false