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

From Anil Jangam
Subject Resue of the sql dump to create new database.
Date
Msg-id F73B646FCC01D5118F530002B32C337263B254@INDTS_FS
Whole thread Raw
List pgsql-cygwin
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

*******************************************************

pgsql-cygwin by date:

Previous
From: Dave Page
Date:
Subject: Re: Setting unique constraints.
Next
From: Dave Page
Date:
Subject: Re: Resue of the sql dump to create new database.