pg_get_viewdef() and full joins of tables with identical column names - Mailing list pgsql-bugs

I am running PostgreSQL 7.3.4.  The problem I am having can be
demonstrated on the following minimal case based on the full join
example from the PostgreSQL User's Guide (Section 4.2.1.1):

   create database foo;
   \connect foo
   create table t1 (num integer, name char (1));
   create table t2 (num integer, value char (3));
   --
   -- the example in the User's Guide has:
   -- SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
   --
   -- this will not work:
   -- create view t12 as select * from t1 full join t2 on t1.num = t2.num;
   -- ERROR:  CREATE TABLE: attribute "num" duplicated
   --
   -- but this works:
   create view t12 (num1, name, num2, value)
       as
     select * from t1 full join t2 on t1.num = t2.num;

The view thus created works as one would expect, but restoring from the
dump fails:

   $ pg_dump foo > pg_dump.foo
   $ dropdb foo
   DROP DATABASE
   $ createdb foo
   CREATE DATABASE
   $ psql -q -v ON_ERROR_STOP= -f pg_dump.foo foo
   psql:pg_dump.foo:37:ERROR:  Column reference "num" is ambiguous

The offending command goes like this:

   CREATE VIEW t12 AS
       SELECT num AS num1, name, num AS num2, value FROM (t1 FULL JOIN t2 ON ((t1.num = t2.num)));

This seems to be a bug in the way Postgres reports the view
definition.  The server must itself know which table each column comes
from, otherwise the view would not have worked.

Is this a known problem?  Is there a simple workaround -- can the
error be made to go away without renaming the columns, maybe by
re-formulating the "create view" command somehow clever?

Thanks,
Toomas.