How can I use a default value in a Select query in PostgreSQL?

Question

I would like to use a default value for a column that should be used if no rows is returned. Is that possible in PostgreSQL? How can I do it? Or is there any other way I can solve this?

E.g. something like this:

SELECT MAX(post_id) AS max_id DEFAULT 0 FROM my_table WHERE org_id = 3

And if there is no rows with org_id = 3 in the table I want to return 0.

Answer 1

SELECT coalesce(MAX(post_id),0) AS max_id FROM my_table WHERE org_id = 3

or

SELECT case count(*) when 0 then 0 else MAX(post_id) end AS max_id
FROM my_table 
WHERE org_id = 3;

if you want max(post_id) to be null when there is 1 row but post_id is null

_dbfiddle

Answer 2

If you want to show 0 (alas 1 row) when your query returns 0 rows, then you could use:

SELECT COALESCE( 
 ( SELECT MAX(post_id) FROM my_table WHERE org_id = 3 )
 , 0) AS max_id

• lolung: How can this be done for more than one column?

Answer 3

SELECT 
 coalesce(MAX(post_id),0) AS max_id 
FROM 
 my_table 
WHERE 
 org_id = 3 

The above do not work if you want to use default name for name field and it works only if you use number field . The below query works for all type of fields..

SELECT 
 COALESCE(
 (SELECT column_name FROM my_table WHERE id = -1), 
 'default_value'
 ) AS column_name;

Answer 4

I cannot get either of the above to work.

Here is what I found to work for this:

SELECT COALESCE(A.max_id, B.dflt) FROM (
SELECT MAX(post_id) AS max_id FROM my_table WHERE org_id = 3) A
 RIGHT OUTER JOIN (SELECT 0 AS dflt) B
 ON 1 = 1

I realize not an elegant solution but does the job.

• Jonas: SELECT coalesce(MAX(post_id),0) AS max_id FROM my_table WHERE org_id = 3 works fine for me.
• Jack Douglas: @mmandk9 can you elaborate on "doesn't work" - what version of postgres are you on and what error message (if any) do you get?

Answer 5

Just return the default value if no rows are found:

SELECT IFNULL(s_stock, 5)
 FROM stock_info 
 WHERE s_product_id = '43';

• Erwin Brandstetter: IFNULL is not valid syntax in Postgres (or standard SQL). It's used in MySQL.
• Fabian Pijcke: If no row is found, nothing will be returned.