Re: BUG #1453: NULLs in UNION query

I've also come across this in 7.4. You could also use:

SELECT NULL AS Test
UNION ALL SELECT NULL::int
UNION ALL SELECT 0

Dirk

Tom Lane wrote:

>"" <m(dot)woehling(at)barthauer(dot)de> writes:
>
>
>>The following query should not raise an error ("ERROR: UNION types text and
>>integer cannot be matched"):
>>
>>
>
>
>
>>SELECT NULL AS Test
>>UNION ALL SELECT NULL
>>UNION ALL SELECT 0
>>
>>
>
>Hmm ... it works if you do
>
>SELECT NULL AS Test
>UNION ALL (SELECT NULL
>UNION ALL SELECT 0)
>
>The problem is that transformSetOperationTree() resolves the column
>datatypes one UNION pair at a time, and so the two NULLs default to
>"text" before we ever look at the zero.
>
>It's probably possible to rejigger it so that the common type is chosen
>considering all the set-operation arms in parallel, but it doesn't seem
>like a trivial change. (Translation: there will not be an immediate
>fix.)
>
>As a workaround, perhaps you could cast one or all of the nulls to int
>explicitly:
>
>SELECT NULL::int AS Test
>UNION ALL SELECT NULL
>UNION ALL SELECT 0
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
>