[HACKERS] type problems during union: NULL+NULL produces TEXT

[Michael Wildpaner]

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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly