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.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
