0

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';
  • 1
    Like this:? SELECT title FROM gallery WHERE title % 'Nation' ORDER BY SIMILARITY(title, 'Nation') DESC LIMIT 5; – ypercubeᵀᴹ May 18 '15 at 17:31
  • 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. – apasajja May 18 '15 at 19:35
  • 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/… – Erwin Brandstetter May 18 '15 at 20:01
4

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:

Also relevant:

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.