49

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?

nintschger
  • 1,786
  • 5
  • 30
  • 45

4 Answers4

106

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 = ...
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
7

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.
Csongor Halmai
  • 3,239
  • 29
  • 30
1

Try this,

Select NULLIF(Max(code_id), 0) +1 
from  configentries 
WHERE configtable_id = ...
Eduardo Cuomo
  • 17,828
  • 6
  • 117
  • 94
rurugg
  • 33
  • 1
  • 3
    `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`. – gabrielf Oct 18 '22 at 12:02
0

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
phowner
  • 11
  • 2