Re: Resue of the sql dump to create new database. - Mailing list pgsql-cygwin

I think you've found a bug in pgAdmin which I'll look to fix for the next
release.. Can you try editting the dump file to add double quotes arround
the column names in the fk definition eg.

CREATE TABLE "DataAccessIdentifier" (
  "DataAccessID" int2 NOT NULL,
  "PrimaryComponentID" int2 NOT NULL,
  "SecondaryComponentID" int2 NOT NULL,
  CONSTRAINT "DataAccessIdentifier_pkey" PRIMARY KEY ("DataAccessID"),
  CONSTRAINT "PrimaryCompID" FOREIGN KEY ("PrimaryComponentID") REFERENCES
"FunctionEntityInventory" ("ComponentID") ON DELETE CASCADE ON UPDATE
CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT "SecondaryCompID" FOREIGN KEY ("SecondaryComponentID")
REFERENCES "FunctionEntityInventory" ("ComponentID") ON DELETE CASCADE ON
UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE );

Does that fix it?

Regards, Dave.

> -----Original Message-----
> From: Anil Jangam [mailto:anilj@indts.com]
> Sent: 18 December 2001 09:33
> To: 'Dave Page'; pgsql-cygwin@postgresql.org
> Subject: Resue of the sql dump to create new database.
> Importance: High
>
>
> Hi,
>
> I am trying to use the exiting sql dump (stored in a file
> test.sql) as follows to create the database tables in another
> database. Now, When I run this using command "psql -U uss -d
> enterprisedata -f test.sql" , I get the following error.
>
> NOTE: There is alreay a database exist named "enterprise"
> with same properties. I am just trying to create a mirror
> image in another database under new owner.
>
> What is wrong with this?? Where as when it is created using
> pgAdmin, it doesn't give this kind of error message. I hope
> we should be able to use the .sql file content to create the
> database. (This test.sql file is beiong generated with "File
> -> Save DB Schema" option in the pull down menu. Also pg_dump
> command can be used on the prompt.)
>
> -anil.
>
> *********** Content of test.sql file *****************
>
> CREATE TABLE "FunctionEntityInventory" (
>   "ComponentID" int2 NOT NULL,
>   "EntityType" varchar(25),
>   "EntityName" varchar(25),
>   "RedundencyRequired" char(1),
>   CONSTRAINT "FunctionEntityInventory_pkey" PRIMARY KEY
> ("ComponentID") ); REVOKE ALL ON "FunctionEntityInventory"
> FROM PUBLIC; GRANT ALL ON "FunctionEntityInventory" TO "uss";
> COMMENT ON TABLE "FunctionEntityInventory" IS 'This table is
> the central repository for all the compnents in the network.
> The components here are UCM and USC.';
>
> CREATE TABLE "DataAccessIdentifier" (
>   "DataAccessID" int2 NOT NULL,
>   "PrimaryComponentID" int2 NOT NULL,
>   "SecondaryComponentID" int2 NOT NULL,
>   CONSTRAINT "DataAccessIdentifier_pkey" PRIMARY KEY
> ("DataAccessID"),
>   CONSTRAINT "PrimaryCompID" FOREIGN KEY (PrimaryComponentID)
> REFERENCES "FunctionEntityInventory" (ComponentID) ON DELETE
> CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
>   CONSTRAINT "SecondaryCompID" FOREIGN KEY
> (SecondaryComponentID) REFERENCES "FunctionEntityInventory"
> (ComponentID) ON DELETE CASCADE ON UPDATE CASCADE NOT
> DEFERRABLE INITIALLY IMMEDIATE ); REVOKE ALL ON
> "DataAccessIdentifier" FROM PUBLIC; GRANT ALL ON
> "DataAccessIdentifier" TO "uss"; COMMENT ON TABLE
> "DataAccessIdentifier" IS 'This table defines the data access
> identifier, which is a common interface for primary and
> secondary components. Thus both the components looks at the
> same data.';
>
> *******************************************************
>
> **************** Error message **********************
>
> $ psql -U uss -d enterprisedata -f test.sql
> psql:test.sql:7: NOTICE:  CREATE TABLE/PRIMARY KEY will
> create implicit index 'F unctionEntityInventory_pkey' for
> table 'FunctionEntityInventory' CREATE CHANGE CHANGE COMMENT
>
>   =============== upto this it creates the
> 'FunctionEntityInventory' table in the database ==================
>
> psql:test.sql:19: NOTICE:  CREATE TABLE/PRIMARY KEY will
> create implicit index ' DataAccessIdentifier_pkey' for table
> 'DataAccessIdentifier'
> psql:test.sql:19: NOTICE:  CREATE TABLE will create implicit
> trigger(s) for FOREIGN KEY check(s)
> psql:test.sql:19: ERROR:  columns referenced in foreign key
> constraint not found.
> psql:test.sql:20: ERROR:  Relation 'DataAccessIdentifier'
> does not exist
> psql:test.sql:21: ERROR:  Relation 'DataAccessIdentifier'
> does not exist
> psql:test.sql:22: ERROR:  pg_ownercheck: class
> "DataAccessIdentifier" not found
>
> *******************************************************
>