Message-ID: <4F96F360.60803@gmx.net>
Date: Tue, 24 Apr 2012 20:39:28 +0200
From: Andreas <maps.on@gmx.net>
User-Agent: Mozilla/5.0 (Windows NT 5.1;
	rv:11.0) Gecko/20120327 Thunderbird/11.0.1
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: How to group by similarity?
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I'm trying to get an idea about pg_trgrm.
I created a GIST index on a text column in a table.
Now I can filter the table with similarity().

How would I group the table so that it shows groups that have similarity 
() > x ?

Lets say the table looks like this:

id,  txt
1,   aa1
2,   bb1
3,   cc1
4,   bb2
5,   bb3
6,   aa2
...

How would a select look like that shows:

id,  txt,      group_id
1,   aa1,   1,
6,   aa2,   1,
2,   bb1,   2,
4,   bb2,   2,
5,   bb3,   2,
3,   cc1,   3


An extension of this problem would be to find similar records in 2 tables.
As a result should apear a list of every record from table2 that is 
similar to a record of table1.
Something like:
table1.id,   table2.id
1,     3
1,     5
1,     7
2,     2
2,    11