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

Re: relation between tables

From: Todd Lewis <lewis-todd(at)sbcglobal(dot)net>
To: Mauricio Siqueira de Oliveira <mauricio(dot)oliveiraa(at)uol(dot)com(dot)br>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: relation between tables
Date: 2005-03-29 04:10:18
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
Michael Fuhr wrote:

>On Mon, Mar 28, 2005 at 08:30:54PM -0300, Mauricio Siqueira de Oliveira wrote:
>>Is there any command to display relations between tables? Like, for 
>>instance, I would like to know what column in what table is linked with 
>>a particular column in other table.
>In psql you can use "\d tablename" to see a table's definition,
>including foreign key constraints.  If you run "psql -E" or execute
>"\set ECHO_HIDDEN" then you can see the SQL queries that psql runs
>to get that information, and from those queries you can figure out
>how to write your own queries.  You'll probably want to be familiar
>with the "System Catalogs" chapter of the documentation and the
>"System Information Functions" or "Miscellaneous Functions" section
>of the "Functions and Operators" chapter.
>Here's an example that might show what you're looking for:
>SELECT conrelid::regclass AS relname,
>       conname,
>       pg_get_constraintdef(oid) AS condef
>FROM pg_constraint
>WHERE contype = 'f'
>ORDER BY conrelid, conname;
That only works if they took the time to build the constraints into the 
table definitions. Monitoring already existing queries is probably your 
best bet. Esp if the output it is generating is accepted by the user 
community. I have a database that I support where there is no 
documentation, no constraints built into the tables, and scant knowledge 
on how the system should work. Most knowledge went out the door with the 
contractors who designed it. 6 schema designed by 6 contractors, each 
with a different idea on how things should be designed. Needless to say 
the wheel has been invented about six times. Why talking to each other 
and using grant never crossed their minds, I'll never know. They were 
all gone when I inherited this mess. To top it off after a few months in 
production the accounting being generated started to be questioned (ie 
no longer accepted by the user community).

Document as you go, map it out so that you and someone else can 
understand it. It may take longer upfront, but after a few months you'll 
have a reference document instead of having to re-learn the 
relationships again, and again.

In response to

pgsql-novice by date

Next:From: John DeSoiDate: 2005-03-29 05:37:54
Subject: Re: Windows 2000 Installation Issue
Previous:From: Michael FuhrDate: 2005-03-29 00:50:19
Subject: Re: relation between tables

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