PostgreSQL return default row on empty result set

Question

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?

Answer 1

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.