sqlite IFNULL() in postgres

user_4479510 0
Viewed 63552

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?

ifnull postgresql sqlite
4 Answers

Try this,

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

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

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
user_85278160
Related