From: Joe Abbate <jma@freedomcircle.com>
To: pgsql-sql@postgresql.org
Subject: UPDATE with correlated aggregates
Date: Thu, 26 Jan 2006 22:17:05 -0500
Message-ID: <43D990B1.6090608@freedomcircle.com> (raw)
Hi,
I have a database with one "main" table with three columns that
self-reference the primary key, plus four other tables that have (in
total) seven foreign key columns referencing "main". I want to create a
table (or view) that summarizes the various reference counts, per row in
"main". I have an approach that works, for the much simpler situation
described below, but wanted feedback on possible improvements or
simplifications before implementing it on the actual tables.
Here are the simplified "main" table and one of the ancillary tables:
dev=> \d main
Table "public.main"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
parent | integer |
data | text |
Indexes:
"main_pkey" PRIMARY KEY, btree (id)
dev=> \d items
Table "public.items"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
main | integer |
data | text |
Indexes:
"items_pkey" PRIMARY KEY, btree (id)
dev=> select * from main;
id | parent | data
----+--------+--------
1 | | ABC
2 | 1 | ABCDEF
3 | 1 | ABCGHI
4 | | PQR
5 | 4 | PQRSTU
6 | 4 | PQRUVW
7 | 4 | PQRXYZ
(7 rows)
dev=> select * from items;
id | main | data
----+------+------
1 | 2 | asdf
2 | 2 | jkl;
3 | 2 | qwer
4 | 3 | uiop
5 | 3 | m,./
6 | 4 | zxcv
7 | 4 | rtyu
8 | 4 | fghj
9 | 4 | vbnm
10 | 6 | asl;
11 | 7 | qwop
12 | 7 | zx./
(12 rows)
This is the summary table:
dev=> \d summ
Table "public.summ"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
subs | bigint |
items | bigint |
Indexes:
"summ_pkey" PRIMARY KEY, btree (id)
I first populate "summ" with 'select id from main'. Since an UPDATE
apparently cannot include aggregates, i.e., subs = count(*), I chose to
create two views as follows:
CREATE VIEW main_summ AS
SELECT parent AS id, count(*) AS subs
FROM main
WHERE parent IS NOT NULL
GROUP BY parent;
CREATE VIEW items_summ AS
SELECT items.main AS id, count(*) AS items
FROM items
GROUP BY items.main;
Then I update "summ" as follows:
update summ set subs = ms.subs
from main_summ ms where ms.id = summ.id;
update summ set items = its.items
from items_summ its where its.id = summ.id;
The end result is:
dev=> select * from summ order by id;
id | subs | items
----+------+-------
1 | 2 |
2 | | 3
3 | | 2
4 | 3 | 4
5 | |
6 | | 1
7 | | 2
(7 rows)
In the real database, I'd probably have to create 10 views and do 10
updates so any simpler approach would be appreciated. I played with
creating an intermediate table that was populated from UNION SELECTs of
the various GROUP BYs, each with an additional column that coded the
type of relationship, but couldn't figure out how to create the summary
since the coded column has to be used to add the count to either the
"subs" or "items" columns (or subcolumns by type).
Thanks for any suggestions and comments.
Joe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jma@freedomcircle.com
Subject: Re: UPDATE with correlated aggregates
In-Reply-To: <43D990B1.6090608@freedomcircle.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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