pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedForeign key columns
2+ messages / 2 participants
[nested] [flat]
* Foreign key columns
@ 2009-11-05 21:08 Svenne Krap <svenne.lists@krap.dk>
0 siblings, 1 reply; 2+ messages in thread
From: Svenne Krap @ 2009-11-05 21:08 UTC (permalink / raw)
To: pgsql-sql
Hi.
Is there a simple way to get foreign key data... for example I found a
view, that does what I want ...
It delivers
fk_table | fk_column | pk_table | pk_column | constraint_name
--------------+--------------------+-------------------+-----------+--------------------------------------
organisation | customer_rep | person | id | organisation_customer_rep_fkey
organisation | ekstra_skema | ekstra_skema | id | org_schema_fkey
organisation | in_group | organisation | id | organisation_in_group_fkey
organisation | org_paying_company | organisation | id | organisation_org_paying_company_fkey
organisation | primary_contact | person | id | primary_contact_fkey
organisation | type | organisation_type | id | organisation_type_fkey
The query in question is
SELECT FK.TABLE_NAME as FK_Table, CU.COLUMN_NAME as FK_Column,
PK.TABLE_NAME as PK_Table, PT.COLUMN_NAME as PK_Column,
C.CONSTRAINT_NAME as Constraint_Name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME
= FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON
C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME =
CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON
i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY') PT ON PT.TABLE_NAME =
PK.TABLE_NAME WHERE FK.TABLE_NAME='organisation' ORDER BY 1,2,3,4;
The only problem is that this query is sloooooow, runs in tens of seconds...
Is there a good native (i.e. fast) pgsql-query to find that type of
information?
Svenne
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Foreign key columns
@ 2009-11-05 22:21 Scott Marlowe <scott.marlowe@gmail.com>
parent: Svenne Krap <svenne.lists@krap.dk>
0 siblings, 0 replies; 2+ messages in thread
From: Scott Marlowe @ 2009-11-05 22:21 UTC (permalink / raw)
To: Svenne Krap <svenne.lists@krap.dk>; +Cc: pgsql-sql
On Thu, Nov 5, 2009 at 2:08 PM, Svenne Krap <svenne.lists@krap.dk> wrote:
> Hi.
>
> Is there a simple way to get foreign key data... for example I found a
> view, that does what I want ...
>
> It delivers
>
> fk_table | fk_column | pk_table | pk_column | constraint_name
>
> --------------+--------------------+-------------------+-----------+--------------------------------------
>
> organisation | customer_rep | person | id | organisation_customer_rep_fkey
>
> organisation | ekstra_skema | ekstra_skema | id | org_schema_fkey
>
> organisation | in_group | organisation | id | organisation_in_group_fkey
>
> organisation | org_paying_company | organisation | id | organisation_org_paying_company_fkey
>
> organisation | primary_contact | person | id | primary_contact_fkey
>
> organisation | type | organisation_type | id | organisation_type_fkey
>
>
>
> The query in question is
>
> SELECT FK.TABLE_NAME as FK_Table, CU.COLUMN_NAME as FK_Column,
> PK.TABLE_NAME as PK_Table, PT.COLUMN_NAME as PK_Column,
> C.CONSTRAINT_NAME as Constraint_Name
> FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
> INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME
> = FK.CONSTRAINT_NAME
> INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON
> C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
> INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME =
> CU.CONSTRAINT_NAME
> INNER JOIN (
> SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
> INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON
> i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
> WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY') PT ON PT.TABLE_NAME =
> PK.TABLE_NAME WHERE FK.TABLE_NAME='organisation' ORDER BY 1,2,3,4;
>
>
> The only problem is that this query is sloooooow, runs in tens of seconds...
It runs in 112 milliseconds on my machine. Maybe your catalogs are
extremely bloated?
> Is there a good native (i.e. fast) pgsql-query to find that type of
> information?
This one seems to work pretty well. If you want to see a query to
find such things, the easy way is to start psql with the -E switch,
and issue a \d command on the organisation table and steal the SQL
from there. That query will be pgsql specific, and possibly / likely
pgsql VERSION dependent, so know that going into it.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2009-11-05 22:21 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-11-05 21:08 Foreign key columns Svenne Krap <svenne.lists@krap.dk>
2009-11-05 22:21 ` Scott Marlowe <scott.marlowe@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox