pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPrimary key reference count
7+ messages / 3 participants
[nested] [flat]
* Primary key reference count
@ 2006-04-19 12:01 Wiebe Cazemier <halfgaar@gmx.net>
2006-04-19 13:09 ` Re: Primary key reference count A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-04-19 17:21 ` Re: Primary key reference count Volkan YAZICI <yazicivo@ttnet.net.tr>
0 siblings, 2 replies; 7+ messages in thread
From: Wiebe Cazemier @ 2006-04-19 12:01 UTC (permalink / raw)
To: pgsql-sql
Hi,
I'm trying to find which of the tables in pg_catalog contains the amount of
references to a primary key, but I can't seem to find it. Google queries are
also less than successful. Is it even stored in the catalog?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Primary key reference count
2006-04-19 12:01 Primary key reference count Wiebe Cazemier <halfgaar@gmx.net>
@ 2006-04-19 13:09 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-04-19 13:33 ` Re: Primary key reference count Wiebe Cazemier <halfgaar@gmx.net>
1 sibling, 1 reply; 7+ messages in thread
From: A. Kretschmer @ 2006-04-19 13:09 UTC (permalink / raw)
To: pgsql-sql
am 19.04.2006, um 14:01:03 +0200 mailte Wiebe Cazemier folgendes:
> Hi,
>
> I'm trying to find which of the tables in pg_catalog contains the amount of
> references to a primary key, but I can't seem to find it. Google queries are
> also less than successful. Is it even stored in the catalog?
Yes,
http://www.postgresql.org/docs/8.1/interactive/catalog-pg-constraint.html
HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Primary key reference count
2006-04-19 12:01 Primary key reference count Wiebe Cazemier <halfgaar@gmx.net>
2006-04-19 13:09 ` Re: Primary key reference count A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2006-04-19 13:33 ` Wiebe Cazemier <halfgaar@gmx.net>
2006-04-19 15:00 ` Re: Primary key reference count Wiebe Cazemier <halfgaar@gmx.net>
0 siblings, 1 reply; 7+ messages in thread
From: Wiebe Cazemier @ 2006-04-19 13:33 UTC (permalink / raw)
To: pgsql-sql
On Wednesday 19 April 2006 15:09, A. Kretschmer wrote:
> Yes,
> http://www.postgresql.org/docs/8.1/interactive/catalog-pg-constraint.html
Either I'm blind, or this is not what I'm looking for. I need to know the
amount of references _to_ a primary key. If you have a customer with two
orders and one invoice, the reference count for the primary key of the
custormer would be 3.
I need to know if I can delete rows which are not referenced anywhere, without
catching the relational constraint exception. The reason I can't catch the
exception, is that I want the exception to arrive at my application and/or
tests.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Primary key reference count
2006-04-19 12:01 Primary key reference count Wiebe Cazemier <halfgaar@gmx.net>
2006-04-19 13:09 ` Re: Primary key reference count A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-04-19 13:33 ` Re: Primary key reference count Wiebe Cazemier <halfgaar@gmx.net>
@ 2006-04-19 15:00 ` Wiebe Cazemier <halfgaar@gmx.net>
2006-04-19 15:47 ` Re: Primary key reference count Wiebe Cazemier <halfgaar@gmx.net>
0 siblings, 1 reply; 7+ messages in thread
From: Wiebe Cazemier @ 2006-04-19 15:00 UTC (permalink / raw)
To: pgsql-sql
On Wednesday 19 April 2006 15:33, Wiebe Cazemier wrote:
> Either I'm blind, or this is not what I'm looking for. I need to know the
> amount of references _to_ a primary key. If you have a customer with two
> orders and one invoice, the reference count for the primary key of the
> custormer would be 3.
>
> I need to know if I can delete rows which are not referenced anywhere,
> without catching the relational constraint exception. The reason I can't
> catch the exception, is that I want the exception to arrive at my application
> and/or tests.
Never mind. I found another solution.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Primary key reference count
2006-04-19 12:01 Primary key reference count Wiebe Cazemier <halfgaar@gmx.net>
2006-04-19 13:09 ` Re: Primary key reference count A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-04-19 13:33 ` Re: Primary key reference count Wiebe Cazemier <halfgaar@gmx.net>
2006-04-19 15:00 ` Re: Primary key reference count Wiebe Cazemier <halfgaar@gmx.net>
@ 2006-04-19 15:47 ` Wiebe Cazemier <halfgaar@gmx.net>
0 siblings, 0 replies; 7+ messages in thread
From: Wiebe Cazemier @ 2006-04-19 15:47 UTC (permalink / raw)
To: pgsql-sql
On Wednesday 19 April 2006 17:00, Wiebe Cazemier wrote:
> Never mind. I found another solution.
Whoops. Posted to quickly there. I do still need to know the amount of
references to the primary key.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Primary key reference count
2006-04-19 12:01 Primary key reference count Wiebe Cazemier <halfgaar@gmx.net>
@ 2006-04-19 17:21 ` Volkan YAZICI <yazicivo@ttnet.net.tr>
2006-04-23 15:27 ` Re: Primary key reference count Wiebe Cazemier <halfgaar@gmx.net>
1 sibling, 1 reply; 7+ messages in thread
From: Volkan YAZICI @ 2006-04-19 17:21 UTC (permalink / raw)
To: Wiebe Cazemier <halfgaar@gmx.net>; +Cc: pgsql-sql
On Apr 19 02:01, Wiebe Cazemier wrote:
> I'm trying to find which of the tables in pg_catalog contains the amount of
> references to a primary key, but I can't seem to find it.
Here's simple query, to list which table's which columns references to a
specific table. (Hope this is what you asked for.)
SELECT TBL.table_name, COL.column_name
FROM information_schema.referential_constraints AS REF
INNER JOIN information_schema.table_constraints AS TBL
USING (constraint_name)
INNER JOIN information_schema.constraint_column_usage AS COL
USING (constraint_name)
INNER JOIN information_schema.table_constraints AS PRI
ON (PRI.constraint_name = REF.unique_constraint_name)
WHERE PRI.table_name = 'pri_id'; -- Your target table goes here.
This is a quick & dirty hack. Just wanted to show the possibility of the
idea using information schema. (You don't want to deal with internal
tables, ain't?)
Regards.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Primary key reference count
2006-04-19 12:01 Primary key reference count Wiebe Cazemier <halfgaar@gmx.net>
2006-04-19 17:21 ` Re: Primary key reference count Volkan YAZICI <yazicivo@ttnet.net.tr>
@ 2006-04-23 15:27 ` Wiebe Cazemier <halfgaar@gmx.net>
0 siblings, 0 replies; 7+ messages in thread
From: Wiebe Cazemier @ 2006-04-23 15:27 UTC (permalink / raw)
To: pgsql-sql
On Wednesday 19 April 2006 19:21, Volkan YAZICI wrote:
> Here's simple query, to list which table's which columns references to a
> specific table. (Hope this is what you asked for.)
>
> SELECT TBL.table_name, COL.column_name
> FROM information_schema.referential_constraints AS REF
> INNER JOIN information_schema.table_constraints AS TBL
> USING (constraint_name)
> INNER JOIN information_schema.constraint_column_usage AS COL
> USING (constraint_name)
> INNER JOIN information_schema.table_constraints AS PRI
> ON (PRI.constraint_name = REF.unique_constraint_name)
> WHERE PRI.table_name = 'pri_id'; -- Your target table goes here.
>
> This is a quick & dirty hack. Just wanted to show the possibility of the
> idea using information schema. (You don't want to deal with internal
> tables, ain't?)
I know the reply is somewhat late, but indeed, this would seem to be what I was
looking for. And, using the information schema instead of pg_catalog would
seem to be a better idea as well :)
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-04-23 15:27 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-19 12:01 Primary key reference count Wiebe Cazemier <halfgaar@gmx.net>
2006-04-19 13:09 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-04-19 13:33 ` Wiebe Cazemier <halfgaar@gmx.net>
2006-04-19 15:00 ` Wiebe Cazemier <halfgaar@gmx.net>
2006-04-19 15:47 ` Wiebe Cazemier <halfgaar@gmx.net>
2006-04-19 17:21 ` Volkan YAZICI <yazicivo@ttnet.net.tr>
2006-04-23 15:27 ` Wiebe Cazemier <halfgaar@gmx.net>
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