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

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.

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';