From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: zj4204@gmail.com
Subject: BUG #19756: ALTER COLUMN TYPE USING gives wrong values on partitions with IS NULL
Date: Tue, 13 Oct 2026 17:24:13 +0000
Message-ID: <19756-046a8a872ba33d27@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19756
Logged by: kalakala
Email address: zj4204@gmail.com
PostgreSQL version: 18.6
Operating system: Debian 13 (official Docker image postgres:18.6)
Description:
ALTER TABLE ... ALTER COLUMN ... TYPE ... USING stores wrong values in
partitions and inheritance children whose column numbers differ from the
parent's (here the parent has a dropped column). This happens when the USING
expression contains IS [NOT] NULL, IS [NOT] TRUE/FALSE/UNKNOWN, or a named
function argument.
CREATE TABLE pu (x int, k int, a int, b int, c int) PARTITION BY LIST (k);
ALTER TABLE pu DROP COLUMN x;
CREATE TABLE pu1 PARTITION OF pu FOR VALUES IN (1);
INSERT INTO pu VALUES (1, 5, NULL, 9);
ALTER TABLE pu ALTER COLUMN a TYPE bigint USING CASE WHEN b IS NULL THEN -1
ELSE a END;
SELECT k, a, b, c FROM pu;
k | a | b | c
---+---+---+---
1 | 5 | | 9
Expected a = -1, because b is NULL. The same statements on a table without
partitions give a = -1, and so does 12.22. With num_nulls(b) = 1 in place of
b IS NULL, the result is also -1. b IS UNKNOWN and b IS NOT TRUE (with
boolean b and c) give the same wrong result.
With a named argument on the same layout, USING extract(day from
make_interval(days => b))::int on a row with b = 2, c = 30 sets a = 30. The
positional form make_interval(0, 0, 0, b) sets a = 2.
Two variants fail with an internal error instead:
CREATE TABLE r (a int);
CREATE TABLE r1 () INHERITS (r);
ALTER TABLE r ALTER COLUMN a TYPE int USING (1 IS NULL)::int;
ERROR: XX000: unrecognized node type: 7
LOCATION: transformExprRecurse, parse_expr.c:376
CREATE TABLE p1 (x int, a int, b int);
ALTER TABLE p1 DROP COLUMN x;
CREATE TABLE c1 () INHERITS (p1);
INSERT INTO c1 VALUES (30, NULL);
ALTER TABLE p1 ALTER COLUMN a TYPE int USING (b IS NULL)::int;
ERROR: XX000: attribute number 3 exceeds number of columns 2
LOCATION: CheckVarSlotCompatibility, execExprInterp.c:2411
The first of these fails although r1 has the same column layout as r.
Reproduced on 18.6, 19beta4 and 13.23 (PGDG Debian packages in the official
Docker images). 12.22 gives the expected result in every case above.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, zj4204@gmail.com
Subject: Re: BUG #19756: ALTER COLUMN TYPE USING gives wrong values on partitions with IS NULL
In-Reply-To: <19756-046a8a872ba33d27@postgresql.org>
References: <19756-046a8a872ba33d27@postgresql.org>
* 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