Thread: create definiton
is it possible to find out, which create-statement i have used to create a certain table? if so, how? thx.
pintman@gmx.de (Marco) writes:
> is it possible to find out, which create-statement i have used to
> create a certain table? if so, how?
pg_dump -s -t tablename dbname
will reconstruct the table schema for you.
regards, tom lane
On Sat, Jun 15, 2002 at 06:36:29AM -0700, Marco <pintman@gmx.de> wrote a message of 4 lines which said: > is it possible to find out, which create-statement i have used to > create a certain table? if so, how? Not the exact one but "pg_dump --schema-only $DB" is close enough.
Marco wrote: > is it possible to find out, which create-statement i have used to > create a certain table? if so, how? Yes, pg_dump has options to dump only the schema for an individual table. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Yeah, but the gotcha there is that any foreign keys are not saved as a FOREIGN KEY statement, just as the INSERT/UPDATE trigger that enforces the foreign key. So if you dump the schema, drop the table (which deletes the foreign keys in the foreign keyed tables), alter the dump and reload: the triggers to enforce the foreign key validation that reside in the tables the foreign key is keyed into do not get recreated, so your referential integrity is only enforced WRT changes to your table reloaded from the dump, and NOT enforced from the tables the keys go into. Please correct me if I am wrong, because if there is a better way I would like to know, I deal with specs changing all the time and have to drop tables to change them from NULL to NOT NULL etc. Terry Fielder Network Engineer Great Gulf Homes / Ashton Woods Homes terry@greatgulfhomes.com > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane > Sent: Monday, June 17, 2002 10:19 AM > To: Marco > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] create definiton > > > pintman@gmx.de (Marco) writes: > > is it possible to find out, which create-statement i have used to > > create a certain table? if so, how? > > pg_dump -s -t tablename dbname > > will reconstruct the table schema for you. > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org >
On Mon, 2002-06-17 at 12:43, terry@greatgulfhomes.com wrote:
> Yeah, but the gotcha there is that any foreign keys are not saved as a
> FOREIGN KEY statement, just as the INSERT/UPDATE trigger that enforces the
> foreign key. So if you dump the schema, drop the table (which deletes the
> foreign keys in the foreign keyed tables), alter the dump and reload: the
> triggers to enforce the foreign key validation that reside in the tables the
> foreign key is keyed into do not get recreated, so your referential
> integrity is only enforced WRT changes to your table reloaded from the
> dump, and NOT enforced from the tables the keys go into.
I believe this is incorrect. A schema dump will include the triggers.
from my latest schema dump (this morning):
<snip>
--
-- TOC Entry ID 373 (OID 1133843)
--
-- Name: "RI_ConstraintTrigger_1133842" Type: TRIGGER Owner: tjenkins
--
CREATE CONSTRAINT TRIGGER "mandatorytraininggroupid_mandat" AFTER DELETE
ON "mandatorytraininggroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH
ROW EXECUTE PROCEDURE "RI_FKey_cascade_del"
('mandatorytraininggroupid_mandat', 'mandatorytraining',
'mandatorytraininggroup', 'UNSPECIFIED', 'mandatorytraininggroupid',
'mandatorytraininggroupid');
<snip>
--
-- TOC Entry ID 460 (OID 1133543)
--
-- Name: "RI_ConstraintTrigger_1133542" Type: TRIGGER Owner: tjenkins
--
CREATE CONSTRAINT TRIGGER "educationemployee_employee_fk" AFTER UPDATE
ON "employee" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_noaction_upd" ('educationemployee_employee_fk',
'education', 'employee', 'FULL', 'educationemployee', 'employeeid');
<snip>
>
> Please correct me if I am wrong, because if there is a better way I would
> like to know, I deal with specs changing all the time and have to drop
> tables to change them from NULL to NOT NULL etc.
)
--
Tom Jenkins
Development InfoStructure
http://www.devis.com
There are 2 sides to a foreign key e.g.
if invoices.user_id is foreign keyed to users.user_id
When you add an invoice, invoices trigger(s) check to make sure the user_id
is valid in the users table. (call it trigger set A)
When you update/delete a user in users, users triggers make sure that the
user_id is not used in the invoices table, if it is the trigger(s) stop you
from update/deleting that user_id in the users table (call it trigger set B)
When you pg_dump a table, the dump contains the SQL statements to create
trigger set A, but NOT trigger set B.
When you drop the table both trigger set A and trigger set B are deleted.
Then, when I alter the schema and reload the table, I have the issue that
only trigger set A is reloaded, hence the referential integrity is only
enforced from the table invoices, not from changes to the table users.
I can give you a more precise example for illustration if you like...
Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Jenkins
> Sent: Monday, June 17, 2002 3:37 PM
> To: terry@greatgulfhomes.com
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] create definiton
>
>
> On Mon, 2002-06-17 at 12:43, terry@greatgulfhomes.com wrote:
> > Yeah, but the gotcha there is that any foreign keys are not
> saved as a
> > FOREIGN KEY statement, just as the INSERT/UPDATE trigger
> that enforces the
> > foreign key. So if you dump the schema, drop the table
> (which deletes the
> > foreign keys in the foreign keyed tables), alter the dump
> and reload: the
> > triggers to enforce the foreign key validation that reside
> in the tables the
> > foreign key is keyed into do not get recreated, so your referential
> > integrity is only enforced WRT changes to your table
> reloaded from the
> > dump, and NOT enforced from the tables the keys go into.
>
> I believe this is incorrect. A schema dump will include the triggers.
> from my latest schema dump (this morning):
> <snip>
> --
> -- TOC Entry ID 373 (OID 1133843)
> --
> -- Name: "RI_ConstraintTrigger_1133842" Type: TRIGGER Owner: tjenkins
> --
>
> CREATE CONSTRAINT TRIGGER "mandatorytraininggroupid_mandat"
> AFTER DELETE
> ON "mandatorytraininggroup" NOT DEFERRABLE INITIALLY
> IMMEDIATE FOR EACH
> ROW EXECUTE PROCEDURE "RI_FKey_cascade_del"
> ('mandatorytraininggroupid_mandat', 'mandatorytraining',
> 'mandatorytraininggroup', 'UNSPECIFIED', 'mandatorytraininggroupid',
> 'mandatorytraininggroupid');
>
> <snip>
> --
> -- TOC Entry ID 460 (OID 1133543)
> --
> -- Name: "RI_ConstraintTrigger_1133542" Type: TRIGGER Owner: tjenkins
> --
>
> CREATE CONSTRAINT TRIGGER "educationemployee_employee_fk" AFTER UPDATE
> ON "employee" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
> PROCEDURE "RI_FKey_noaction_upd" ('educationemployee_employee_fk',
> 'education', 'employee', 'FULL', 'educationemployee', 'employeeid');
>
> <snip>
>
> >
> > Please correct me if I am wrong, because if there is a
> better way I would
> > like to know, I deal with specs changing all the time and
> have to drop
> > tables to change them from NULL to NOT NULL etc.
>
>
> )
> --
>
> Tom Jenkins
> Development InfoStructure
> http://www.devis.com
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>