Thread: data type of projected item in a union not correct

On Sep 24, 2012, at 19:43, the6campbells <the6campbells@gmail.com> wrote:

> Database 9.0.4
> Driver PostgreSQL 9.1 JDBC4 (build 902)
>
>
> The following projection is not described with the expected type+precision as one would expect from ISO-SQL
>
> Is this a known Postgres bug, quirk or ....
>
> t1.c1 char(32)
> t2.c1 varchar(32)
>
> select t1.c1 returns precision of 32
> select t2.c1 returned precision of 32
>
>
> t1.c1
> union
> t2.c1
>
> t2.c1
> union
> t1.c1
>
> returns precision 2147483647
>
>

From the description here

http://www.postgresql.org/docs/9.2/interactive/typeconv-union-case.html

I infer that since the two types are not the same they are both converted to the preferred base type "text" which while
itshould have an undefined precision instead is reported to have an arbitrary large precision. 

What result do you feel it should report and for what reason?

David J.

On Mon, 24 Sep 2012, the6campbells wrote:

> The following projection is not described with the expected type+precision
> as one would expect from ISO-SQL
>
> Is this a known Postgres bug, quirk or ....

This is a serverside problem.  Try the same test in psql or similar.

jurka=# create table t as select 'a'::varchar(32) as c1,
'b'::varchar(32) as c2 union select 'c'::char(32),
'd'::varchar(32);
SELECT 2
jurka=# \d t
              Table "public.t"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 c1     | character varying     |
 c2     | character varying(32) |


As you can see the c1 column has had the length information dropped.

Kris Jurka