How to group by similarity?

From: Andreas <maps(dot)on(at)gmx(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: How to group by similarity?
Date: 2012-04-24 18:39:28
Message-ID: 4F96F360.60803@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Steve Crawford 2012-04-24 18:56:06 Re: [SQL] Simple way to get missing number
Previous Message Emi Lu 2012-04-24 18:10:24 Re: [SQL] Simple way to get missing number