Thread
-
pg_dump problem
Dave Bodenstab <imdave@mcs.net> — 1999-02-27T22:33:30Z
============================================================================ 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: ---------------------------------------------------------------------