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;