From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | Durumdara <durumdara(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Field size become unlimited in union... |
Date: | 2016-05-03 14:23:19 |
Message-ID: | CAKFQuwYZUhp9DvLJqj9fSwfu3Cm3go7_q1kyAgwjmNhKCertbQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, May 3, 2016 at 6:50 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:
> 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).
>>
>>
The maximum size of the unknown 'a' as text is unlimited so it did choose
the maximum field size max(INF, 100) = 100
The system recognizes there is no guarantee that 'a' could be reliably
casted into a varchar(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
>
>>
>>
This seems to fail to answer the OPs question. Specifically, do these
rules automatically, or at least if #1 is not true, cause typemod
information to be lost? IOW, is it because of the unknown that both end up
up-casted to typemod-less text?
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | dandl | 2016-05-03 14:32:04 | Re: Does the initial postgres user have a password? |
Previous Message | Nicolas Paris | 2016-05-03 14:15:35 | Re: postgresql & Fulltext & ranking & my own functions |