69

What is the equivalent of SQLite's IFNULL() in Postgres?

I have to following query (sqlite in Ruby):

SELECT ifnull(max(code_id) + 1, 1) 
FROM configentries 
WHERE configtable_id = ...

How should this look like if I want the same result with PostgreSQL?

Improve this question
2
  • 1
    try coalesce - it takes first not null value Commented May 12, 2017 at 9:31
  • @a_horse_with_no_name No, it's not to generate a unique ID. Commented May 12, 2017 at 9:50

4 Answers 4

Reset to default
133

try coalesce:

The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null

SELECT coalesce(max(code_id) + 1, 1) 
FROM configentries 
WHERE configtable_id = ...
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

16

The short answer is that COALESCE function is what you can use in postgres.

COALESCE is better than IFNULL for several reasons:

  • COALESCE is a standard SQL function (implemented in ~every RDBMS), while IFNULL is not standard, even if widely used.
  • COALESCE can handle more than two operands. It returns the first non-NULL value. For example, COALESCE(NULL, 'a', NULL) returns a. IFNULL cannot do this.
Improve this answer

Comments

2

All answers are good, but wil only work in situations where only one row is returned.

If you want to query multiple rows and receive a default value if 0 Rows are found, you can use this:

SELECT example_field from "example_table" WHERE attribute='x'
UNION 
SELECT 'my_default_value' FROM  "example_table"  WHERE 
(SELECT example_field from "example_table" WHERE attribute='x'  LIMIT 1) is NULL
Improve this answer

Comments

-2

Try this,

Select NULLIF(Max(code_id), 0) +1 
from  configentries 
WHERE configtable_id = ...
Improve this answer

1 Comment

nullif and ifnull are very different functions. ifnull returns the second argument if the first is null while nullif returns null if the two arguments are equal. The original query would return 1 if code_id is null while the query with nullif would return null.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.