Thread: relation between tables

Hi All,

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. I´m working on a database not so
well organized and to make my queries I have to "SELECT * FROM"  every
single table to see if it is linked with the one I´m working with, which
is a hell of a work!

Thanks in  advance,

Mauricio

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;

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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.