PostgreSQL version: 18.1 (also verified on 18.2)
OS: Linux
Description:
============
When a view uses a USING join on columns with different integer types (integer vs
bigint) and the SELECT clause contains an explicit narrowing cast, pg_get_viewdef()
produces SQL that PostgreSQL itself rejects. This makes pg_dump produce dumps that
fail on restore for any such view.
The SELECT clause gets col::integer, while the GROUP BY gets (col::bigint) — they
refer to the same underlying column but with different casts, so the GROUP BY check
fails to recognise the SELECT expression as covered.
Minimal reproducer:
===================
CREATE TABLE t1 (year integer, val numeric);
CREATE TABLE t2 (year bigint, label text);
INSERT INTO t1 VALUES (2025, 100), (2025, 200), (2026, 300);
INSERT INTO t2 VALUES (2025, 'A'), (2026, 'B');
-- View creation succeeds and queries work fine:
CREATE VIEW v AS
SELECT year::integer AS year, t2.label, sum(val) AS total
FROM t1
LEFT JOIN t2 USING (year)
GROUP BY year, t2.label;
SELECT * FROM v; -- returns correct results
-- pg_get_viewdef output:
SELECT pg_get_viewdef('v'::regclass, true);
Output of pg_get_viewdef:
=========================
SELECT t1.year::integer AS year,
t2.label,
sum(t1.val) AS total
FROM t1
LEFT JOIN t2 USING (year)
GROUP BY (t1.year::bigint), t2.label;
Note: SELECT has t1.year::integer, GROUP BY has (t1.year::bigint).
Attempting to re-execute the pg_get_viewdef output fails:
=========================================================
CREATE VIEW v2 AS
SELECT t1.year::integer AS year,
t2.label,
sum(t1.val) AS total
FROM t1
LEFT JOIN t2 USING (year)
GROUP BY (t1.year::bigint), t2.label;
ERROR: column "t1.year" must appear in the GROUP BY clause or be used
in an aggregate function
LINE 2: SELECT t1.year::integer AS year,
^
Impact:
=======
pg_dump uses pg_get_viewdef() to serialise view definitions. Any database
containing such a view (USING join between integer and bigint columns, with
an explicit cast in SELECT) will produce a dump that fails during restore with
the above error.
Without the explicit cast in SELECT (i.e. just SELECT year, ...) pg_get_viewdef
emits t1.year::bigint in both SELECT and GROUP BY, and the round-trip works.
The bug is triggered specifically by the narrowing cast (bigint -> integer) in
the SELECT list combined with a USING join.
Kind regards,
Swirl
