pg_get_viewdef() and full joins of tables with identical column names

From: toomas(at)tklabor(dot)ee (Toomas Rosin)
To: pgsql-bugs(at)postgresql(dot)org
Subject: pg_get_viewdef() and full joins of tables with identical column names
Date: 2003-11-30 07:36:20
Message-ID: 20031130073620.4B395BD83@ns.tklabor.ee
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Browse pgsql-bugs by date

  From Date Subject
Next Message Bruce Momjian 2003-11-30 16:04:25 Re: [BUGS] Bug in byteaout code in all PostgreSQL versions
Previous Message Bruce Momjian 2003-11-30 05:29:10 Re: unix_socket_directory vs pg_ctl?