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

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


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

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;