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: | Raw Message | Whole Thread | 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) |