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(at)mudupalli(dot)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`.