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:
"" <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
