14

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
  • 2
    Not quite so. I found this question by googling "postgresql <->" Nov 2, 2018 at 21:55

1 Answer 1

18

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

The manual:

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

The module has to be installed (once per database) with:

CREATE EXTENSION pg_trgm;

More details:

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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