1

Does anyone know what is the effect of % operator on varchar?

I found this query in a project and don't really know what it is doing:

SELECT * FROM Location l where l.name % :param;

I supposed that it has the same effect that:

SELECT * FROM Location l where l.name LIKE '%:param%'

But I didn't find the explanation on PostgreSQL documentation.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Matthieu Saleta
  • 1,388
  • 1
  • 11
  • 17

1 Answers1

4

% is the "similarity" operator, provided by the additional module pg_trgm.

It takes text (or other string types) as left and right operand and returns boolean: true if both operands are similar enough, false if not. The threshold is set with the GUC parameter pg_trgm.similarity_threshold.

Related:

Not to be confused with the modulo operator %. Same symbol, but the mathematical operator takes numeric types as left and right operand.

In Postgres, operators are defined by the operator name (like %) plus left and right operands. Gory details in the manual chapter Operator Type Resolution. The casual user hardly needs to know any of this. Typically, it just works.

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • oh ok... I'm trying to make some unit tests on an H2 in-memory database with Postgresql mode. I reproduced the similarity function that is not natively supported by H2 but i don't know if i could reproduce the operator... – Matthieu Saleta Sep 22 '17 at 05:43
  • @MatthieuSaleta: Follow [the link to the manual I provided](https://www.postgresql.org/docs/current/static/pgtrgm.html). There is a description of what it does exactly. – Erwin Brandstetter Sep 22 '17 at 06:03