PostgreSQL select with default value

I recently ran into a situation in which I wanted to select data from a table, and wanted a default value in case the data does not exist in the table. The straightforward solution is to use a CASE statement in the SELECT query like this:

SELECT CASE
WHEN (SELECT count(*) FROM my_table WHERE my_field = ‘my_value’) > 0
THEN my_value_field
ELSE ‘my_default_value’
END
FROM my_table WHERE my_field = ‘my_value’

However this straightforward solution is not efficient. The query on my_table happens twice: once when counting rows that match the query, and once when selecting the value if a matching row is found.

I was convinced that there has to be a simple way to get the same result with only one search in the table. After fiddling with different ideas for a while I came up with the following solution:

— If the first argument is not null, return it. Otherwise return the default value (second argument).
CREATE OR REPLACE FUNCTION add_default(INTEGER, INTEGER)
RETURNS INTEGER AS
$$
SELECT
CASE WHEN $1 is null
THEN $2
ELSE $1
END
$$
LANGUAGE SQL
IMMUTABLE;

This function receives the integer output of a query. If this integer value is not null – it is returned. But if the input is null (no rows were found in the output of the query) – the default value (second argument) is returned. It can be used like this:

SELECT add_default((SELECT my_value_field FROM my_table WHERE my_field = ‘my_value’), 17)

And what if your query returns a float and not an integer? Just overload the function with float inputs and outputs:

CREATE OR REPLACE FUNCTION add_default(FLOAT, FLOAT)
RETURNS FLOAT AS
$$
…

Note that this function only works with queries that output a single value.
If you have any other solutions – please share them in the comments section.

Enjoy!
Yuval.