agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedSingle SQL to find if any record exists across several tables
3+ messages / 3 participants
[nested] [flat]
* Single SQL to find if any record exists across several tables
@ 2017-09-21 12:45 =?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?= <ertan.kucukoglu@1nar.com.tr>
2017-09-21 15:06 ` Re: Single SQL to find if any record exists across several tables David G. Johnston <david.g.johnston@gmail.com>
2017-09-27 12:07 ` Re: Single SQL to find if any record exists across several tables Feike Steenbergen <feikesteenbergen@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Ertan Küçükoðlu @ 2017-09-21 12:45 UTC (permalink / raw)
To: pgsql-sql
Hello,
Using PostgreSQL 9.6.5 64bit running on Windows 10 64bit.
There are no foreign key across tables in database. There is a table where
company codes are kept. Most other tables includes company code value in a
column. Column name is same for all tables which is "fk".
Before deleting any company code row, I would like to be sure that it is not
used in anywhere. Currently I am running a separate query for each table but
that takes a lot of time and possibility to miss a table or two.
Is it possible to build a single SQL statement and include all tables in it
so that I receive single "count(fk)" sum across all tables?
Several sample SQLs run at the moment are as follows:
Select count(fk) from cari
Select count(fk) from sipbaslik
Select count(fk) from stkdepo
Select count(fk) from stkkart
Select count(fk) from stkhar
Thanks & regards,
Ertan Küçükoðlu
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Single SQL to find if any record exists across several tables
2017-09-21 12:45 Single SQL to find if any record exists across several tables =?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?= <ertan.kucukoglu@1nar.com.tr>
@ 2017-09-21 15:06 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2017-09-21 15:06 UTC (permalink / raw)
To: Ertan Küçükoğlu <ertan.kucukoglu@1nar.com.tr>; +Cc: pgsql-sql
On Thu, Sep 21, 2017 at 5:45 AM, Ertan Küçükoğlu <
ertan.kucukoglu@1nar.com.tr> wrote:
>
> Is it possible to build a single SQL statement and include all tables in it
> so that I receive single "count(fk)" sum across all tables?
>
> Several sample SQLs run at the moment are as follows:
> Select count(fk) from cari
> Select count(fk) from sipbaslik
> Select count(fk) from stkdepo
> Select count(fk) from stkkart
> Select count(fk) from stkhar
>
Without delving into dynamic SQL you can do either:
SELECT sum(count) FROM
(
SELECT count(fk) FROM can
UNION ALL
SELECT count(fk) FROM sipbaslik
) src
OR
SELECT
(SELECT count(fk) FROM can)
+
(SELECT count(fk) FROM sipbaslik)
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Single SQL to find if any record exists across several tables
2017-09-21 12:45 Single SQL to find if any record exists across several tables =?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?= <ertan.kucukoglu@1nar.com.tr>
@ 2017-09-27 12:07 ` Feike Steenbergen <feikesteenbergen@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Feike Steenbergen @ 2017-09-27 12:07 UTC (permalink / raw)
To: Ertan Küçükoğlu <ertan.kucukoglu@1nar.com.tr>; +Cc: pgsql-sql
On 21 September 2017 at 14:45, Ertan Küçükoğlu <ertan.kucukoglu@1nar.com.tr>
wrote:
>
> Before deleting any company code row, I would like to be sure that it is
not
> used in anywhere.
If you don't actually care about the count, but only if the value exists
(like the
subject suggests) you could do something like:
SELECT EXISTS (
SELECT FROM cari WHERE fk = $1
UNION ALL
SELECT FROM sipbaslik WHERE fk = $1
[UNION ALL ...]
);
This will stop executing as soon as it finds a record matching fk, it does
not have to scan subsequent tables if it hits on the first table.
Depending on your data set that might be very useful.
=# EXPLAIN ANALYZE
SELECT EXISTS (
SELECT FROM cari WHERE fk = 'a'
UNION ALL
SELECT FROM sipbaslik WHERE fk = 'a'
);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Result (cost=3.87..3.88 rows=1 width=1) (actual time=0.010..0.010 rows=1
loops=1)
InitPlan 1 (returns $0)
-> Append (cost=0.00..54.14 rows=14 width=0) (actual
time=0.009..0.009 rows=1 loops=1)
-> Seq Scan on cari (cost=0.00..27.00 rows=7 width=0) (actual
time=0.009..0.009 rows=1 loops=1)
Filter: (fk = 'a'::text)
-> Seq Scan on sipbaslik (cost=0.00..27.00 rows=7 width=0)
(never executed)
Filter: (fk = 'a'::text)
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2017-09-27 12:07 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-09-21 12:45 Single SQL to find if any record exists across several tables =?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?= <ertan.kucukoglu@1nar.com.tr>
2017-09-21 15:06 ` David G. Johnston <david.g.johnston@gmail.com>
2017-09-27 12:07 ` Feike Steenbergen <feikesteenbergen@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox