Postgres: Disable "similarity" column output on "SELECT SIMILARITY"?

Question

I've tried disabling similarity column output on SELECT SIMILARITY but still cannot.

Below query will generate a column similarity:

SELECT SIMILARITY(title, 'Nation'), title
FROM gallery
WHERE title % 'Nation'
ORDER BY similarity DESC LIMIT 5;

Below query does not output similarity column but I am unable to sort:

SELECT title FROM gallery WHERE title % 'Nation';

score 4 · Accepted Answer · edited May 23 '17 at 12:40

Generally, you can always wrap a query as subquery if you don't want to output all rows:

SELECT title FROM (SELECT ...) sub;

But you can also use expressions in ORDER BY, not just input or output columns. So there is no need for this (like @ypercube already commented).

For the case at hand, it must be mentioned that you are using the additional module pg_trgm. What you are trying to do can be simplified to:

SELECT title
FROM   gallery
WHERE  title % 'Nation'
ORDER  BY title <-> 'Nation'
LIMIT  5;

I replaced SIMILARITY(title, 'Nation') DESC with the simpler, equivalent title <-> 'Nation', since the <-> operator (per documentation):

Returns the "distance" between the arguments, that is one minus the similarity() value.

Either version works, but the latter is shorter and index support is bound to the operator, not the function. And you should have a GiST index on title.

Details:

PostgreSQL FTS and Trigram-similarity Query Optimization

Also relevant:

Finding similar strings with PostgreSQL quickly

Postgres: Disable "similarity" column output on "SELECT SIMILARITY"?

1 Answers1