29

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.

38
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

10

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

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.

  • 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
  • 2
    @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
-2

Just return the default value if no rows are found:

SELECT IFNULL(s_stock, 5)
  FROM stock_info 
 WHERE s_product_id = '43';
  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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