Search code examples
postgresqlpattern-matchingoperators

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.

Solution

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