sqlite IFNULL() in postgres

Question

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?

Hagiography

try coalesce - it takes first not null value – Instructions

@a_horse_with_no_name No, it's not to generate a unique ID. – Hagiography

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

Instructions

Answer 2

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.

Cotquean

Answer 3

Try this,

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

Intercommunion

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. – Steer

Answer 4

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

Lawhorn