Improving postgres subquery using coalesce

Picture this: two tables, cases and case_messages. The case can be any number of messages or not. One of these messages may be the final answer in the event. Now I want to return a list of cases with a bool column that tells me if there is a definitive answer or not. If there are no messages at all, the request should return "false". Only if there is at least one related message in case_messages, where isfinalanswer = true, if the request returns true. I managed to get this working with coalesce and a sub-query, but it looks super ugly:

SELECT, cases.title,
COALESCE((SELECT isfinalanswer FROM case_messages WHERE caseid = ORDER BY isfinalanswer DESC LIMIT 1), false) AS hasfinalanswer,
FROM cases;


How can I improve this?


source to share

2 answers

I would recommend using exists


SELECT, c.title,
       (exists (SELECT 1 FROM case_messages cm WHERE cm.caseid = and isfinalanswer = true)
       ) as hasfinalanswer
FROM cases c;


I don't know if you would think it is less ugly.

I must add that for performance (which is not mentioned in the question) you need an index on case_messages(caseid, isfinalanswer)

. With an index like this, this is likely to be the most efficient solution.



A correlated subquery (a query in a select list) can be troublesome in performance. Make left join

anddistinct on

select distinct on (, c.title,
    coalesce(cm.isfinalanswer, false) as hasfinalanswer
    cases c
    left join
    case_messages cm on cm.caseid =
order by 1, 3 desc


In this case, coalesce

you can replace withis true

    cm.isfinalanswer is true as hasfinalanswer


Edit: To avoid the cost order by

required distinct on

, do pre-aggregation

select id, title, coalesce(isfinalanswer, false) as hasfinalanswer
    cases c
    left join (
        select caseid as id, bool_or(isfinalanswer) as isfinalanswer
        from case_messages
        group by 1
    ) cm using (id)




All Articles