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?

4 Answers

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

Try this,

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

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 

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.