CODEWITHSUNDEEP
postgresqlpattern-matchingoperators
Matthieu Saleta
Matthieu Saleta

Reputation: 1518

PostgreSQL '%' operator

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.

Upvotes: 1

Views: 578

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658707

% 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:

Upvotes: 4

Related Questions