From: Andreas <maps.on@gmx.net>
To: pgsql-sql@postgresql.org
Subject: How to group by similarity?
Date: Tue, 24 Apr 2012 20:39:28 +0200
Message-ID: <4F96F360.60803@gmx.net> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: maps.on@gmx.net
Subject: Re: How to group by similarity?
In-Reply-To: <4F96F360.60803@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox