pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18962: Type Conversion incorrect when performing UNION of queries.
4+ messages / 4 participants
[nested] [flat]
* BUG #18962: Type Conversion incorrect when performing UNION of queries.
@ 2025-06-19 12:21 PG Bug reporting form <noreply@postgresql.org>
2025-06-19 15:44 ` Re: BUG #18962: Type Conversion incorrect when performing UNION of queries. Tom Lane <tgl@sss.pgh.pa.us>
2025-06-19 15:46 ` BUG #18962: Type Conversion incorrect when performing UNION of queries. David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: PG Bug reporting form @ 2025-06-19 12:21 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: sundar@mudupalli.com
The following bug has been logged on the website:
Bug reference: 18962
Logged by: Sundar Mudupalli
Email address: sundar@mudupalli.com
PostgreSQL version: 17.5
Operating system: Ubuntu I believe (using a Cloud SQL instance in GC
Description:
Take the following table definitions:
```
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
;
create table public.test_table_2 (
char_fixed character,
char_fixed_len character(5),
char_var character varying(5),
text_column text );
```
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`. However running the following query produces the following:
```
select table_name, column_name, data_type, character_maximum_length from
information_schema.columns where table_name like 'test_table_%' order by
table_name;
table_name | column_name | data_type |
character_maximum_length
--------------+----------------+-------------------+--------------------------
test_table_1 | text_type | character |
test_table_2 | char_fixed | character |
1
test_table_2 | char_fixed_len | character |
5
test_table_2 | char_var | character varying |
5
test_table_2 | text_column | text |
(5 rows)
```
The data type for text says `character` (fixed length character string) of
undetermined length, when it should really be of type `text` as the column
`text_column` in `test_table_2`.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18962: Type Conversion incorrect when performing UNION of queries.
2025-06-19 12:21 BUG #18962: Type Conversion incorrect when performing UNION of queries. PG Bug reporting form <noreply@postgresql.org>
@ 2025-06-19 15:44 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2025-06-19 15:44 UTC (permalink / raw)
To: sundar@mudupalli.com; +Cc: pgsql-bugs@lists.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
^ permalink raw reply [nested|flat] 4+ messages in thread
* BUG #18962: Type Conversion incorrect when performing UNION of queries.
2025-06-19 12:21 BUG #18962: Type Conversion incorrect when performing UNION of queries. PG Bug reporting form <noreply@postgresql.org>
@ 2025-06-19 15:46 ` David G. Johnston <david.g.johnston@gmail.com>
2025-06-20 05:39 ` Re: BUG #18962: Type Conversion incorrect when performing UNION of queries. Sundar Mudupalli <sundar@mudupalli.com>
1 sibling, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2025-06-19 15:46 UTC (permalink / raw)
To: sundar@mudupalli.com <sundar@mudupalli.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Thursday, June 19, 2025, PG Bug reporting form <noreply@postgresql.org>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18962
> Logged by: Sundar Mudupalli
> Email address: sundar@mudupalli.com
> PostgreSQL version: 17.5
> Operating system: Ubuntu I believe (using a Cloud SQL instance in GC
> Description:
>
> Take the following table definitions:
> ```
> 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
> ;
> create table public.test_table_2 (
> char_fixed character,
> char_fixed_len character(5),
> char_var character varying(5),
> text_column text );
> ```
> 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`.
We typically choose to suggest just avoiding “character” instead of trying
to document why the observed behavior is correct…
In short, "character no length restriction" is actually the documented type
"bpchar".
"character length restricted" is effectively a domain over "bpchar" - rule 2
"text" implicitly converts to "bpchar" - rule 5 vice-versa exception
Thus the final result is "bpchar".
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18962: Type Conversion incorrect when performing UNION of queries.
2025-06-19 12:21 BUG #18962: Type Conversion incorrect when performing UNION of queries. PG Bug reporting form <noreply@postgresql.org>
2025-06-19 15:46 ` BUG #18962: Type Conversion incorrect when performing UNION of queries. David G. Johnston <david.g.johnston@gmail.com>
@ 2025-06-20 05:39 ` Sundar Mudupalli <sundar@mudupalli.com>
0 siblings, 0 replies; 4+ messages in thread
From: Sundar Mudupalli @ 2025-06-20 05:39 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Tom and David,
Thank you for the quick and clear response. I did not realize that fixed
length char and text were convertible one to the other. Conversion to fixed
length char could result in truncation, so I thought it must be forbidden.
I now realize that the SQL standard decided otherwise many decades ago.
My real issue is with SQLAlchemy - which is not able to handle tables with
bpchar in Postgres (throws an exception). I will follow up and file a bug
against SQLAlchemy. I am the lead developer for a tool to validate tables
across databases -
https://github.com/GoogleCloudPlatform/professional-services-data-validator.
Since we don't design the underlying schemas, we have to support "all" data
types rather than telling users, don't use char, use text instead.
Thanks again.
Sundar Mudupalli
On Thu, Jun 19, 2025 at 9:17 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Thursday, June 19, 2025, PG Bug reporting form <noreply@postgresql.org>
> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 18962
>> Logged by: Sundar Mudupalli
>> Email address: sundar@mudupalli.com
>> PostgreSQL version: 17.5
>> Operating system: Ubuntu I believe (using a Cloud SQL instance in GC
>> Description:
>>
>> Take the following table definitions:
>> ```
>> 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
>> ;
>> create table public.test_table_2 (
>> char_fixed character,
>> char_fixed_len character(5),
>> char_var character varying(5),
>> text_column text );
>> ```
>> 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`.
>
>
> We typically choose to suggest just avoiding “character” instead of trying
> to document why the observed behavior is correct…
>
> In short, "character no length restriction" is actually the documented
> type "bpchar".
> "character length restricted" is effectively a domain over "bpchar" - rule
> 2
> "text" implicitly converts to "bpchar" - rule 5 vice-versa exception
> Thus the final result is "bpchar".
>
> David J.
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-06-20 05:39 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-06-19 12:21 BUG #18962: Type Conversion incorrect when performing UNION of queries. PG Bug reporting form <noreply@postgresql.org>
2025-06-19 15:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-06-19 15:46 ` David G. Johnston <david.g.johnston@gmail.com>
2025-06-20 05:39 ` Sundar Mudupalli <sundar@mudupalli.com>
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