0

For some reason couldn't find anything on the topic that would work for me: Return sample row if result set is empty and similar questions are using integers for joining.

I have a table with simple structure: pk::int, key::varchar, value::varchar

I need to do a search by key column and return the multiple values. In case there is no matching rows (empty result set) I want to return a default value, e.g. the word "default".

Easy solution would be to have a CTE or subquery with UNION and LIMIT 1, but the query should be able to return multiple values (key column may contain duplicates), so this is not a solution.

I have come up with this, which is working:

SELECT value FROM mytable
  WHERE key = 'foobar'
UNION
SELECT 'default' AS value
  WHERE (SELECT COUNT(*) FROM mytable WHERE key = 'foobar') <1

But the subquery is doubling the amount of queries on the data.

Is there a more elegant and performant solution?

1 Answer 1

2

You could use a left join.

Assuming value cannot be null, then you could use coalesce to decide between the real value and the default

with t as (select 'default' value) 
select coalesce(mytable.value, t.value)  from t left join mytable on key='present';

But if value can be NULL, you would need something a bit fancier:

with t as (select 'default' value)
select 
   case when id is not null then mytable.value else t.value end 
from t left join mytable on key='null';

Or to be more obscure, you can make t be a table with 0 columns but one row:

with t as (select)
select 
   case when id is not null then mytable.value else 'default' end 
from t left join mytable on key='null';

... and performant solution

You haven't shown any performance issues in the first place. So it is hard to evaluate if we have fixed them.

2
  • No, the value can't be NULL in my case, but this works really well. So the part I missed is to use coalesce the way you did. Thank you. Considering the performance - well this undoubtedly will be faster than my case as it doesn't execute the query on the table twice (I don't think optimization happens here). Commented Jul 19, 2023 at 6:35
  • The performance question here is more theoretical for now, as the usage of this query is during an insertion of records to the queue type table, about 100/s is the maximum estimated, but needs to be tested in real life scenarios Commented Jul 19, 2023 at 6:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.