type problems during union: NULL+NULL produces TEXT

From: Michael Wildpaner <mike(at)rainbow(dot)studorg(dot)tuwien(dot)ac(dot)at>
To: pgsql-hackers(at)postgresql(dot)org
Subject: type problems during union: NULL+NULL produces TEXT
Date: 2003-01-31 18:59:14
Message-ID: Pine.LNX.4.44.0301311939250.17649-100000@rainbow.studorg.tuwien.ac.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

I have three tables, two of which are missing a column:

CREATE TABLE table1 (t1 TEXT);
CREATE TABLE table2 (t2 TEXT);
CREATE TABLE table3 (t3 TEXT, i3 INTEGER);

I am trying to create a view over these tables that defaults values for
non-existant columns to NULL.

CREATE VIEW view1 (i, t) AS
SELECT t1, NULL FROM table1
UNION ALL
SELECT t2, NULL FROM table2
UNION ALL
SELECT t3, i3 FROM table3
;

This fails with

ERROR: UNION types 'text' and 'integer' not matched

suggesting that NULL+NULL produces TEXT as type of the second column in
the union. The plain select (without CREATE VIEW) fails in the same way.

It works for two tables (NULL+INTEGER = INTEGER):

CREATE VIEW view2 (i, t) AS
SELECT t1, NULL FROM table1
UNION ALL
SELECT t3, i3 FROM table3
;

and of course with explicit casts

CREATE VIEW view3 (i, t) AS
SELECT t1, NULL::integer FROM table1
UNION ALL
SELECT t2, NULL::integer FROM table2
UNION ALL
SELECT t3, i3 FROM table3
;

Best wishes, Mike

PS: This is version()
'PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4'.

--
Life is like a fire. DI Michael Wildpaner
Flames which the passer-by forgets. Ph.D. Student
Ashes which the wind scatters.
A man lived. -- Omar Khayyam

Browse pgsql-hackers by date

  From Date Subject
Next Message Kurt Roeckx 2003-01-31 19:04:46 Re: Linux.conf.au 2003 Report
Previous Message Kurt Roeckx 2003-01-31 18:42:33 Re: [HACKERS] Linux.conf.au 2003 Report