Skip site navigation (1) Skip section navigation (2)

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

From: Anil Jangam <anilj(at)indts(dot)com>
To: "'Dave Page'" <dpage(at)vale-housing(dot)co(dot)uk>, pgsql-cygwin(at)postgresql(dot)org
Subject: Re: Resue of the sql dump to create new database.
Date: 2001-12-18 09:48:55
Message-ID: F73B646FCC01D5118F530002B32C337263B255@INDTS_FS (view raw or flat)
Thread:
Lists: pgsql-cygwin
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
> > 
> > *******************************************************
> > 
> 

pgsql-cygwin by date

Next:From: Dave PageDate: 2001-12-18 13:13:19
Subject: Re: Resue of the sql dump to create new database.
Previous:From: Dave PageDate: 2001-12-18 09:43:53
Subject: Re: Resue of the sql dump to create new database.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group