up vote 2 down vote favorite
share [fb]

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.

link|improve this question
95% accept rate
feedback

2 Answers

up vote 7 down vote accepted 
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

link|improve this answer
Thanks coalesce was great! – Jonas May 18 at 15:07
Good use of COALESCE, +1 !!! – RolandoMySQLDBA Aug 3 at 15:14
feedback
up vote 1 down vote

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.

link|improve this answer
1  
SELECT coalesce(MAX(post_id),0) AS max_id FROM my_table WHERE org_id = 3 works fine for me. – Jonas Oct 21 at 18:27
@mmandk9 can you elaborate on "doesn't work" - what version of postgres are you on and what error message (if any) do you get? – Jack Douglas♦ Oct 21 at 19:37
feedback

Your Answer

 
or
required, but never shown

Not the answer you're looking for? Browse other questions tagged or ask your own question.