From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Durumdara <durumdara(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Field size become unlimited in union... |
Date: | 2016-05-03 13:50:36 |
Message-ID: | bc61f14a-f42f-247f-2107-653d4f110869@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 05/03/2016 04:28 AM, Durumdara wrote:
> Hello!
>
> As I experienced, PGSQL changes the result field sizes to unlimited
> varchar, when I passed different field size in same column of the union.
>
> SP.Key - varchar(100)
> SP.Value - varchar(200)
>
> Example:
>
> select 'a', value from sp
> union all
> select key, value from sp
>
>
> The first field is not limited to 100, it's unlimited varchar() (= Text,
> Memo, CLOB).
>
> So PG don't use the maximal field size (100).
>
>
> If I did cast on the field to resize to 100, the result is limited
> correctly.
>
>
> select cast('a' as varchar(100)), value from sp
> union all
> select key, value from sp
>
>
> Can I force somehow to PG use the maximal size?
>
> Or must I know and equalize all field sizes in union?
>
> Or must I use temporary tables and inserts to not get this problem?
See below for complete explanation:
http://www.postgresql.org/docs/9.5/static/typeconv-union-case.html
10.5. UNION, CASE, and Related Constructs
>
> Thanks
>
> Regards
> dd
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | uğur Karabin | 2016-05-03 13:57:03 | Re: Vacuum full of parent without partitions possible? |
Previous Message | Oleg Bartunov | 2016-05-03 13:50:18 | Re: Insert only table and size of GIN index JSONB field. |