public inbox for [email protected]  

help / color / mirror / Atom feed

From: Swirl Smog Dowry <[email protected]>
To: [email protected]
Subject: pg_get_viewdef() produces non-round-trippable SQL for views with USING join on mismatched integer types
Date: Thu, 26 Feb 2026 11:18:43 +0100
Message-ID: <CA+-gibjCg_vjcq3hWTM0sLs3_TUZ6Q9rkv8+pe2yJrdh4o4uoQ@mail.gmail.com> (raw)

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

reply

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 using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: pg_get_viewdef() produces non-round-trippable SQL for views with USING join on mismatched integer types
  In-Reply-To: <CA+-gibjCg_vjcq3hWTM0sLs3_TUZ6Q9rkv8+pe2yJrdh4o4uoQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox