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

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`.