| From: | Jim Garrison <jim(dot)garrison(at)nwea(dot)org> |
|---|---|
| To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
| Subject: | Complex Recursive Query |
| Date: | 2014-07-23 23:00:21 |
| Message-ID: | 7a401e3043514d939af12f911a9511dc@BN1PR06MB839.namprd06.prod.outlook.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
I have a collection of relationship rows of the form
Table: graph
key1 varchar
key2 varchar
A row of the form ('a','b') indicates that 'a' and 'b' are related.
The table contains many relationships between keys, forming several
disjoint sets. All relationships are bi-directional, and both
directions are present. I.e. the table contains a set of disjoint
graphs specified as node pairs.
For example the set of values
key1 key2
----- -----
a x
a y
b w
c t
x a
y a
y z
z y
t c
w b
w d
d w
defines three disjoint groups of connected keys:
a x y z
c t
b w d
What I would like to achieve is a single SQL query that returns
group key
----- ---
1 a
1 x
1 y
1 z
2 c
2 t
3 b
3 w
3 d
I don't care about preserving the node-to-node relationships, only
the group membership for each node.
I've been playing with "WITH RECURSIVE" CTEs but haven't had any
success. I'm not really sure how to express what I want in SQL, and
it's not completely clear to me that recursive CTEs will help here.
Also I'm not sure how to generate the sequence numbers for the groups
| From | Date | Subject | |
|---|---|---|---|
| Next Message | John R Pierce | 2014-07-23 23:09:32 | Re: Re: Why is unique constraint needed for upsert? (treat atomicity as optional) |
| Previous Message | Seamus Abshere | 2014-07-23 22:58:33 | Re: Re: Why is unique constraint needed for upsert? (treat atomicity as optional) |