'sqlite IFNULL() in postgres

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?

postgresqlsqliteifnull


Solution 1:[1]

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 = ...

Solution 2:[2]

Try this,

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

Solution 3:[3]

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

Solution 4:[4]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 a_horse_with_no_name
Solution 2 Eduardo Cuomo
Solution 3 phowner
Solution 4 Csongor Halmai

Related Questions