CODEWITHSUNDEEP
postgresqlsqliteifnull
user4479510
user4479510

Reputation:

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?

Upvotes: 68

Views: 115329

Answers (4)

rurugg
rurugg

Reputation: 31

Try this,

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

Upvotes: 0

Csongor Halmai
Csongor Halmai

Reputation: 3895

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.

Upvotes: 15

phowner
phowner

Reputation: 21

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

Upvotes: 1

Vao Tsun
Vao Tsun

Reputation: 51476

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

Upvotes: 132

Related Questions