6

I was reading through the Postgres documentation on materialized views and in one example they use the following:

SELECT word FROM words ORDER BY word <-> 'caterpiler' LIMIT 10;

I tried to run a similar query on my own database, but I get an error.

[42883] ERROR: operator does not exist: character varying <-> unknown

I (probably) don't need to use the operator, but I'm just curious what it does.

Notes:

  • I am running the same version as the docs (9.3)
  • I did try Googling the question, but Google just ignores the <-> even if you wrap it in quotes.
  • 1
    Not quite so. I found this question by googling "postgresql <->" – alexkovelsky Nov 2 '18 at 21:55
8

It's supposed to be the "distance" operator from the additional module pg_trgm.

Per documentation:

text <-> text   real    Returns the "distance" between the arguments, that is one minus the similarity() value.

To use it you first have to install the module with:

CREATE EXTENSION pg_trgm;

More details:

Theoretically, any user with enough privileges could create an operator with that operator name using CREATE OPERATOR - but not in the example you link to.

  • Thanks, that explains why it didn't work on my database. I see now that the extention is used in the documentation example. – Villiers Strauss Dec 22 '14 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.