Avoid casting NULLs in UNION...

[Marko Rihtar]

Hi,

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?

thanks for help,
Marko

[Albe Laurenz]

Post by Marko Rihtar
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

[Marko Rihtar]

OK Albe,

thanks for info

Post by Marko Rihtar

Post by Marko Rihtar
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

Post by Marko Rihtar
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