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.
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? |