pg_dump problem - Mailing list pgsql-bugs

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name        : Dave Bodenstab
Your email address    : imdave@mcs.net


System Configuration
---------------------
  Architecture (example: Intel Pentium)      : Intel Pentium

  Operating System (example: Linux 2.0.26 ELF)     : FreeBSD

  PostgreSQL version (example: PostgreSQL-6.4.2)  :   PostgreSQL-6.4.2

  Compiler used (example:  gcc 2.8.0)        : gcc 2.7.2


Please enter a FULL description of your problem:
------------------------------------------------
pg_dump cannot be used to restore a view sometimes.  Apparently, some
qualification is missing as I get the error message:

  ERROR:  Column city is ambiguous




Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
I'm learning sql, and I've created my textbook supplier/parts DB.
A view was produced by:

  create view city_pairs
      as select distinct s.city as scity, p.city as pcity
         from   s, sp, p
         where  s.sno = sp.sno
         and    sp.pno = p.pno;

I then used pg_dump to backup the DB.  When trying to restore the DB, I got
a failure for the city_pairs view.  I've attached the output from pg_dump, and
the results of the attempted restore.

------ pg_dump output ---------
$ pg_dump -f pg_dump.output -d supplierparts
$ cat pg_dump.output
CREATE TABLE "s" (
    "sno" character(5) NOT NULL,
    "sname" character(20),
    "status" int4,
    "city" character(15));
CREATE TABLE "p" (
    "pno" character(6) NOT NULL,
    "pname" character(20),
    "color" character(6),
    "weight" int4,
    "city" character(15));
CREATE TABLE "sp" (
    "sno" character(5) NOT NULL,
    "pno" character(6) NOT NULL,
    "qty" int4);
CREATE TABLE "ls" (
    "sno" character(5),
    "sname" character(20),
    "status" int4);
CREATE TABLE "pq" (
    "pno" character(6),
    "totq" int4);
CREATE TABLE "city_pairs" (
    "scity" character(15),
    "pcity" character(15));
INSERT INTO "s" values ('S1   ','Smith               ',20,'London         ');
INSERT INTO "s" values ('S2   ','Jones               ',10,'Paris          ');
INSERT INTO "s" values ('S3   ','Blake               ',30,'Paris          ');
INSERT INTO "s" values ('S4   ','Clark               ',20,'London         ');
INSERT INTO "s" values ('S5   ','Adams               ',30,'Athens         ');
INSERT INTO "p" values ('P1    ','Nut                 ','Red   ',12,'London         ');
INSERT INTO "p" values ('P2    ','Bolt                ','Green ',17,'Paris          ');
INSERT INTO "p" values ('P3    ','Screw               ','Blue  ',17,'Rome           ');
INSERT INTO "p" values ('P4    ','Screw               ','Red   ',14,'London         ');
INSERT INTO "p" values ('P5    ','Cam                 ','Blue  ',12,'Paris          ');
INSERT INTO "p" values ('P6    ','Cog                 ','Red   ',19,'London         ');
INSERT INTO "sp" values ('S1   ','P1    ',300);
INSERT INTO "sp" values ('S1   ','P2    ',200);
INSERT INTO "sp" values ('S1   ','P3    ',400);
INSERT INTO "sp" values ('S1   ','P4    ',200);
INSERT INTO "sp" values ('S1   ','P5    ',100);
INSERT INTO "sp" values ('S1   ','P6    ',100);
INSERT INTO "sp" values ('S2   ','P1    ',300);
INSERT INTO "sp" values ('S2   ','P2    ',400);
INSERT INTO "sp" values ('S3   ','P2    ',200);
INSERT INTO "sp" values ('S4   ','P2    ',200);
INSERT INTO "sp" values ('S4   ','P4    ',300);
INSERT INTO "sp" values ('S4   ','P5    ',400);
CREATE UNIQUE INDEX "s_pkey" on "s" using btree ( "sno" "bpchar_ops" );
CREATE UNIQUE INDEX "p_pkey" on "p" using btree ( "pno" "bpchar_ops" );
CREATE UNIQUE INDEX "sp_pkey" on "sp" using btree ( "sno" "bpchar_ops", "pno" "bpchar_ops" );
CREATE RULE "_RETls" AS ON SELECT TO "ls" DO INSTEAD SELECT "sno", "sname", "status" FROM "s" WHERE "city" =
'London'::"bpchar";
CREATE RULE "_RETpq" AS ON SELECT TO "pq" DO INSTEAD SELECT "pno", "sum"("qty") AS "totq" FROM "sp" GROUP BY "pno";
CREATE RULE "_RETcity_pairs" AS ON SELECT TO "city_pairs" DO INSTEAD SELECT "city" AS "scity", "city" AS "pcity" FROM
"s","sp", "p" WHERE ("sno" = "sno") AND ("pno" = "pno"); 
-------------------------------




------ attempted restore ------
$ psql supplierparts
Welcome to the POSTGRESQL interactive sql monitor:
  Please read the file COPYRIGHT for copyright terms of POSTGRESQL

   type \? for help on slash commands
   type \q to quit
   type \g or terminate with semicolon to execute query
 You are currently connected to the database: supplierparts

supplierparts=> \d
Couldn't find any tables, sequences or indices!
supplierparts=> \i pg_dump.output
CREATE TABLE "s" (
    "sno" character(5) NOT NULL,
    "sname" character(20),
    "status" int4,
    "city" character(15));
CREATE
CREATE TABLE "p" (
    "pno" character(6) NOT NULL,
    "pname" character(20),
    "color" character(6),
    "weight" int4,
    "city" character(15));
CREATE
CREATE TABLE "sp" (
    "sno" character(5) NOT NULL,
    "pno" character(6) NOT NULL,
    "qty" int4);
CREATE
CREATE TABLE "ls" (
    "sno" character(5),
    "sname" character(20),
    "status" int4);
CREATE
CREATE TABLE "pq" (
    "pno" character(6),
    "totq" int4);
CREATE
CREATE TABLE "city_pairs" (
    "scity" character(15),
    "pcity" character(15));
CREATE
INSERT INTO "s" values ('S1   ','Smith               ',20,'London         ');
INSERT 146755 1
INSERT INTO "s" values ('S2   ','Jones               ',10,'Paris          ');
INSERT 146756 1
INSERT INTO "s" values ('S3   ','Blake               ',30,'Paris          ');
INSERT 146757 1
INSERT INTO "s" values ('S4   ','Clark               ',20,'London         ');
INSERT 146758 1
INSERT INTO "s" values ('S5   ','Adams               ',30,'Athens         ');
INSERT 146759 1
INSERT INTO "p" values ('P1    ','Nut                 ','Red   ',12,'London         ');
INSERT 146760 1
INSERT INTO "p" values ('P2    ','Bolt                ','Green ',17,'Paris          ');
INSERT 146761 1
INSERT INTO "p" values ('P3    ','Screw               ','Blue  ',17,'Rome           ');
INSERT 146762 1
INSERT INTO "p" values ('P4    ','Screw               ','Red   ',14,'London         ');
INSERT 146763 1
INSERT INTO "p" values ('P5    ','Cam                 ','Blue  ',12,'Paris          ');
INSERT 146764 1
INSERT INTO "p" values ('P6    ','Cog                 ','Red   ',19,'London         ');
INSERT 146765 1
INSERT INTO "sp" values ('S1   ','P1    ',300);
INSERT 146766 1
INSERT INTO "sp" values ('S1   ','P2    ',200);
INSERT 146767 1
INSERT INTO "sp" values ('S1   ','P3    ',400);
INSERT 146768 1
INSERT INTO "sp" values ('S1   ','P4    ',200);
INSERT 146769 1
INSERT INTO "sp" values ('S1   ','P5    ',100);
INSERT 146770 1
INSERT INTO "sp" values ('S1   ','P6    ',100);
INSERT 146771 1
INSERT INTO "sp" values ('S2   ','P1    ',300);
INSERT 146772 1
INSERT INTO "sp" values ('S2   ','P2    ',400);
INSERT 146773 1
INSERT INTO "sp" values ('S3   ','P2    ',200);
INSERT 146774 1
INSERT INTO "sp" values ('S4   ','P2    ',200);
INSERT 146775 1
INSERT INTO "sp" values ('S4   ','P4    ',300);
INSERT 146776 1
INSERT INTO "sp" values ('S4   ','P5    ',400);
INSERT 146777 1
CREATE UNIQUE INDEX "s_pkey" on "s" using btree ( "sno" "bpchar_ops" );
CREATE
CREATE UNIQUE INDEX "p_pkey" on "p" using btree ( "pno" "bpchar_ops" );
CREATE
CREATE UNIQUE INDEX "sp_pkey" on "sp" using btree ( "sno" "bpchar_ops", "pno" "bpchar_ops" );
CREATE
CREATE RULE "_RETls" AS ON SELECT TO "ls" DO INSTEAD SELECT "sno", "sname", "status" FROM "s" WHERE "city" =
'London'::"bpchar";
CREATE
CREATE RULE "_RETpq" AS ON SELECT TO "pq" DO INSTEAD SELECT "pno", "sum"("qty") AS "totq" FROM "sp" GROUP BY "pno";
CREATE
CREATE RULE "_RETcity_pairs" AS ON SELECT TO "city_pairs" DO INSTEAD SELECT "city" AS "scity", "city" AS "pcity" FROM
"s","sp", "p" WHERE ("sno" = "sno") AND ("pno" = "pno"); 
ERROR:  Column city is ambiguous
EOF
supplierparts=> \d

Database    = supplierparts
 +------------------+----------------------------------+----------+
 |  Owner           |             Relation             |   Type   |
 +------------------+----------------------------------+----------+
 | imdave           | city_pairs                       | table    |
 | imdave           | ls                               | view?    |
 | imdave           | p                                | table    |
 | imdave           | p_pkey                           | index    |
 | imdave           | pq                               | view?    |
 | imdave           | s                                | table    |
 | imdave           | s_pkey                           | index    |
 | imdave           | sp                               | table    |
 | imdave           | sp_pkey                          | index    |
 +------------------+----------------------------------+----------+




If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------