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:
WHEN (SELECT count(*) FROM my_table WHERE my_field = ‘my_value’) > 0
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
CASE WHEN $1 is null
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.
I had a similar problem to you: http://dba.stackexchange.com/questions/2804/how-can-i-use-a-default-value-in-a-select-query-in-postgresql
I ended up using the built-in function COALESCE(), you may be interested in it.
Thanks for the pointer Jonas!
I think that COALESCE is a better solution than the one I presented. Not only is it a built-in function that automatically works with all data types, but it also supports multiple arguments. For example:
SELECT COALESCE(description, short_description, ‘(none)’) FROM my_table;