Re: Resue of the sql dump to create new database.

Hi Dave,

Yes, its a bug.
It does wrok after addition of the double quotes around the column names in
the FK definitions.
I think it relates to the previous mail reg - "Use of capital letters in the
table name".

-anil.

> -----Original Message-----
> From: Dave Page [mailto:dpage(at)vale-housing(dot)co(dot)uk]
> Sent: Tuesday, December 18, 2001 2:54 PM
> To: 'Anil Jangam'; pgsql-cygwin(at)postgresql(dot)org
> Subject: RE: Resue of the sql dump to create new database.
>
>
> 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(at)indts(dot)com]
> > Sent: 18 December 2001 09:33
> > To: 'Dave Page'; pgsql-cygwin(at)postgresql(dot)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
> >
> > *******************************************************
> >
>