strange behavior of pg_trgm's similarity function

From: Fujii Masao <masao(dot)fujii(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: strange behavior of pg_trgm's similarity function
Date: 2013-10-10 12:03:26
Message-ID: CAHGQGwGkawmurr8v96GjrqFvEhdJx9az77LZbWP8nrPwQtORCg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

The behavior of pg_trgm's similarity function seems strange. Is this
intentional?

I was thinking that the following three calls of the similarity function return
the same number because the second argument is just the three characters
contained in the first argument in every calls.

=# SELECT similarity('12345', '123');
=# SELECT similarity('12345', '234');
=# SELECT similarity('12345', '345');

But that's not true. Each returns the different number.

=# SELECT similarity('12345', '123');
similarity
------------
0.428571
(1 row)

=# SELECT similarity('12345', '234');
similarity
------------
0.111111
(1 row)

=# SELECT similarity('12345', '345');
similarity
------------
0.25
(1 row)

This happens because, for example, similarity('12345', '123') returns
the similarity number of '**12345*' and '**123*' (* means the blank character),
NOT '12345' and '123'. IOW, two and one blank characters are added into
the heading and tailing of each argument, respectively. I wonder why
pg_trgm's similarity function works in this way. We should change this
so that no blank characters are added into the arguments?

Regards,

--
Fujii Masao

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Heikki Linnakangas 2013-10-10 12:12:46 Re: strange behavior of pg_trgm's similarity function
Previous Message Heikki Linnakangas 2013-10-10 11:57:41 Re: GIN improvements part 1: additional information