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:
COALESCEis a standard SQL function (implemented in ~every RDBMS), whileIFNULLis not standard, even if widely used.COALESCEcan handle more than two operands. It returns the first non-NULL value. For example,COALESCE(NULL, 'a', NULL)returnsa.IFNULLcannot do this.
