Обсуждение: Avoid casting NULLs in UNION...

SELECT NULL::integer

UNION ALL

SELECT NULL

UNION ALLSELECT 123


For this sql to work i have to cast NULL to integer.
I was wondering if there exists some configuration parameter at database level that can be used to avoid this need for explicit casting?

thanks for help,
Marko

Marko Rihtar wrote:
> maybe you can help me with this issue.
> Here is an example
>
> SELECT NULL::integer
> UNION ALL
> SELECT NULL
> UNION ALL
> SELECT 123
>
> For this sql to work i have to cast NULL to integer.
> I was wondering if there exists some configuration parameter at database level that can be used to
> avoid this need for explicit casting?

Not that I know of.

The behaviour is well defined and documented, see
http://www.postgresql.org/docs/current/static/typeconv-union-case.html

The UNIONs are computed left to right,
and the data type in the first UNION resolves to "text"
according to rule 2 of the page given above.

You can reorder the UNIONs or use parentheses.

Yours,
Laurenz Albe