From: Tom Lane <tgl@sss.pgh.pa.us>
To: sundar@mudupalli.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18962: Type Conversion incorrect when performing UNION of queries.
Date: Thu, 19 Jun 2025 11:44:03 -0400
Message-ID: <1394964.1750347843@sss.pgh.pa.us> (raw)
In-Reply-To: <18962-71e1ca655df5139f@postgresql.org>
References: <18962-71e1ca655df5139f@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> create table public.test_table_1 as
> SELECT
> 'AA'::character(2) AS text_type
> UNION ALL
> SELECT
> 'BB'::character(2) AS text_type
> UNION ALL
> SELECT
> 'CC'::TEXT AS text_type
> ;
> Based on the [type conversion rules for
> Union](https://www.postgresql.org/docs/current/typeconv-union-case.html),
> the column `text_type` in `test_table_1` should resolve to the postgres data
> type `TEXT`.
I wonder how you read those rules to arrive at that result. The
relevant step is
5. Select the first non-unknown input type as the candidate type,
then consider each other non-unknown input type, left to right.
If the candidate type can be implicitly converted to the other
type, but not vice-versa, select the other type as the new
candidate type. Then continue considering the remaining
inputs. If, at any stage of this process, a preferred type is
selected, stop considering additional inputs.
So we start with char(2) as the candidate type, and nothing changes
when we consider the second UNION arm. When we consider the third
UNION arm, there are implicit casts in both directions between
char(2) and text, so we will not change the candidate type there
either.
I'd be the first to agree that this behavior sometimes yields
non-intuitive results, but we've been using it for ~25 years.
Changing it now seems out of the question.
regards, tom lane
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: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, sundar@mudupalli.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18962: Type Conversion incorrect when performing UNION of queries.
In-Reply-To: <1394964.1750347843@sss.pgh.pa.us>
* 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