Thread: How to easily spot Foreign keys
I am currently doing a bit of 'reverse engineering' and documenting
a set of tables.
Using pgsql and the \d command produces info about most things, but
doesn't *obviously* produce foreign key or references info.
I am aware that the info is buried somewhere in the 'ContraintTrigger'
items, but it isn't obvious how to retrieve the info.
Is there a tool, or a work-around to get this info in a rather more
'user-friendly' fashion?
Regards,
Harry.
On Wed, 13 Aug 2003, Harry Broomhall wrote: > I am currently doing a bit of 'reverse engineering' and documenting > a set of tables. > > Using pgsql and the \d command produces info about most things, but > doesn't *obviously* produce foreign key or references info. I believe 7.3's \d should provide foreign key info (for constraints made in 7.3 or for which you've run adddepend from contrib if you've upgraded from 7.2).
Stephan Szabo writes:
>
> On Wed, 13 Aug 2003, Harry Broomhall wrote:
>
> > I am currently doing a bit of 'reverse engineering' and documenting
> > a set of tables.
> >
> > Using pgsql and the \d command produces info about most things, but
> > doesn't *obviously* produce foreign key or references info.
>
> I believe 7.3's \d should provide foreign key info (for constraints made
> in 7.3 or for which you've run adddepend from contrib if you've upgraded
> from 7.2).
Many thanks for your rapid reply. Unfortunately I am trying to
document some 7.2 based tables in advance of upgrading to 7.3!
Regards,
Harry.
I am under the impression (false or not?) that all version 7.x pg dumps are compatible? So 7.2->7.3 (or 7.4) would not be a problem? > -----Original Message----- > From: pgsql-novice-owner@postgresql.org > [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Harry Broomhall > Sent: Wednesday, August 13, 2003 11:50 AM > To: Stephan Szabo > Cc: harry.broomhall@uk.easynet.net; pgsql-novice@postgresql.org > Subject: Re: [NOVICE] How to easily spot Foreign keys > > > Stephan Szabo writes: > > > > On Wed, 13 Aug 2003, Harry Broomhall wrote: > > > > > I am currently doing a bit of 'reverse engineering' and > documenting > > > a set of tables. > > > > > > Using pgsql and the \d command produces info about most > things, but > > > doesn't *obviously* produce foreign key or references info. > > > > I believe 7.3's \d should provide foreign key info (for constraints made > > in 7.3 or for which you've run adddepend from contrib if you've upgraded > > from 7.2). > > > Many thanks for your rapid reply. Unfortunately I am trying to > document some 7.2 based tables in advance of upgrading to 7.3! > > Regards, > Harry. > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings >
Am Mit, 2003-08-13 um 17.35 schrieb Harry Broomhall: > Is there a tool, or a work-around to get this info in a rather more > 'user-friendly' fashion? pg_dump dbname |grep -i constraint there you will find information about table foreign_table field foreign_field the ordering might actually be different. I wrote scripts several times to automatically convert the triggers to 7.3 style triggers, but always got bored in the end and never finished :) -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
Am Mit, 2003-08-13 um 18.04 schrieb Jason Hihn: > I am under the impression (false or not?) that all version 7.x pg dumps are > compatible? So 7.2->7.3 (or 7.4) would not be a problem? I guess, he wants to convert the triggers to real 7.3 triggers. So he has to check out all existing triggers, drop them and recreate them. Unfortunately it's not trivial to do this automatically. bye -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
On Wed, 13 Aug 2003, Harry Broomhall wrote: > Stephan Szabo writes: > > > > On Wed, 13 Aug 2003, Harry Broomhall wrote: > > > > > I am currently doing a bit of 'reverse engineering' and documenting > > > a set of tables. > > > > > > Using pgsql and the \d command produces info about most things, but > > > doesn't *obviously* produce foreign key or references info. > > > > I believe 7.3's \d should provide foreign key info (for constraints made > > in 7.3 or for which you've run adddepend from contrib if you've upgraded > > from 7.2). > > > Many thanks for your rapid reply. Unfortunately I am trying to > document some 7.2 based tables in advance of upgrading to 7.3! Ah... Hmm, the best documents I can think of relating to this are on techdocs.postgresql.org. The "Compensating for Unimplemented Features in PostgreSQL 7.1" article seems to have a script for generating ALTER TABLE ADD CONSTRAINT commands for foreign keys which may give you some of the info you want.