Re: BUG #18962: Type Conversion incorrect when performing UNION of queries.

Поиск
Список
Период
Сортировка
От Sundar Mudupalli
Тема Re: BUG #18962: Type Conversion incorrect when performing UNION of queries.
Дата
Msg-id CALbjzu4DPCA349R1FHxRvo9Y6+PuASPJ+ijUzVZ0prmvAf4Zow@mail.gmail.com
обсуждение исходный текст
Ответ на BUG #18962: Type Conversion incorrect when performing UNION of queries.  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список pgsql-bugs
Дерево обсуждения
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.

В списке pgsql-bugs по дате отправления: