Field size become unlimited in union...

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, May 3, 2016 at 6:50 AM, Adrian Klaver <adrian.klaver@aklaver.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.

"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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general