Thread: Field size become unlimited in union...
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?
Thanks
Regards
dd
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?
Thanks
Regards
dd
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.klaver@aklaver.com
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.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> 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?
Any mismatch of typmod will result in the merged column being considered
to have no typmod. There is no provision for identifying a "common
superset" typmod. It would have to be some type-specific API, since the
encoding of typmod is type-specific; and we don't have one.
regards, tom lane