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';

• ypercubeᵀᴹ: Like this:? SELECT title FROM gallery WHERE title % 'Nation' ORDER BY SIMILARITY(title, 'Nation') DESC LIMIT 5;
• apasajja: That works but it not following set_limit. When set set_limit value is 0.3, it will get from 0.2. I'm not sure if this is caused by Postgres bug. Thank you for pointing it out.
• Erwin Brandstetter: There must be an additional misunderstanding here. You won't get results with a similarity of 0.2 after running SELECT set_limit(0.3) in the same session. ypercube's query should work. You are aware that the setting only persists for the duration of the current session? Compare: stackoverflow.com/questions/27034559/…

Answer 1

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