division snippets

Avoiding "division by zero" in SQL queries with NULLIF

Tagged division, nullif, postgres, sql, zero  Languages sql

Use NULLIF to avoid “division by zero” in SQL queries when the divisor can have the value zero.

Example:

SELECT
  col_x / NULLIF(col_y, 0) as change,
...

The result will be NULL when col_y contains the value zero.

Preventing division by zero in PostgreSQL

Tagged division, nullif, postgres, zero  Languages sql
localhost=# select 1 / 0;
ERROR:  22012: division by zero
LOCATION:  int4div, int.c:824

Hmmm:

localhost=# select 1 / NULL;
┌──────────┐
│ ?column? │
├──────────┤
│        ¤ │
└──────────┘
(1 row)

Time: 7.067 ms

Ergo:

localhost=# select 1 / NULLIF(0, 0);
┌──────────┐
│ ?column? │
├──────────┤
│        ¤ │
└──────────┘
(1 row)

Time: 0.303 ms