Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

5 Answers 5

up vote 14 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

share|improve this answer
    
Thanks coalesce was great! –  Jonas May 18 '11 at 15:07

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
share|improve this answer

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;

share|improve this answer

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.

share|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 '11 at 18:27
1  
@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 '11 at 19:37

Just return the default value if no rows are found:

SELECT IFNULL(s_stock, 5)
  FROM stock_info 
 WHERE s_product_id = '43';
share|improve this answer
    
IFNULL is not valid syntax in Postgres (or standard SQL). It's used in MySQL. –  Erwin Brandstetter Apr 20 '13 at 13:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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