Complex Recursive Query

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

Responses

Browse pgsql-general by date

  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)