CODEWITHSUNDEEP
postgresql
swami
swami

Reputation: 733

Postgres subquery limits

I have a query which tries to find similar contacts using name, phone and email values. 

select *, ranking function(...) as score from
( select * from contacts where 
    'Dave' <% coalesce(contact_name1, '') 
  UNION
  select * from contacts_index where 
    '9782736623' <% coalesce(phone1, '')   
  UNION
  select * from contacts where 
    '[email protected]' <% coalesce(email1, '')  
  UNION 
  select * from contacts where 
    '26 Ashley Gardens' <% coalesce(address1, '')                          
)  AS sub
limit 1000 order by score desc;

I use the limit 1000 to prevent slow queries in the case where a very common name is queried for (and email, phone, address are null). This results in a huge number of records satisfying the where clause, and needing to be ranked.

However, I want to distribute this limit over the 4 subqueries, otherwise all the 1000 limit could be used up by the name subquery, and no records with similar email, phone or address would make it through.

Unfortunately, it seems that limit cannot be used in a subquery. Does anybody know how I can accomplish this

Upvotes: 0

Views: 7810

Answers (1)

user330315
user330315

Reputation:

This is one of the rare case where you actually need to put a sub-query of a union between parentheses:

select *, ranking function(...) as score 
from
( 
  (
    select * 
    from contacts where  'Dave' <% coalesce(contact_name1, '') 
    limit 250
  )
  UNION
  (
    select * 
    from contacts_index 
    where '9782736623' <% coalesce(phone1, '')   
    limit 250
  )
  UNION
  (
    select * 
    from contacts 
    where '[email protected]' <% coalesce(email1, '')  
    limit 250
  )
  UNION 
  (
    select * 
    from contacts 
    where '26 Ashley Gardens' <% coalesce(address1, '')
    limit 250
)  AS sub
order by score desc;

Upvotes: 3

Related Questions