Division by Zero Is NULL on SQLite and MySQL and an Error on PostgreSQL

A report that divides revenue by visits fails on PostgreSQL the first time a page has no visits. On SQLite, MySQL and MariaDB the same row quietly shows NULL.

PostgreSQL stops the whole statement. SQLite, MySQL and MariaDB answer NULL, MySQL and MariaDB with a warning, and in INSERT and UPDATE with an error when the strict mode includes ERROR_FOR_DIVISION_BY_ZERO (the default). The remainder operator % behaves the same way.

What Each Engine Answered

The example

SELECT 1 / 0;

gave these answers:

What to Write Instead

This form answers the same on every engine:

SELECT 1 / NULLIF(0, 0);

Divide by NULLIF(d, 0): every engine then answers NULL when d is 0.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:1:8: warning: Dividing an integer by an integer drops the fraction on SQLite and PostgreSQL; MySQL and MariaDB keep it. [integer-division]
    1 / 0
    Multiply one side by 1.0 for the fraction and round it to the places you need (see decimal-division): ROUND(7 * 1.0 / 2, 2) is 3.5 on every engine. For a whole number on every engine, take the remainder off first: (7 - 7 % 2) / 2.
example.sql:1:8: error: 1 / 0 is NULL on SQLite, MySQL and MariaDB and an error on PostgreSQL. [division-by-zero]
    1 / 0
    Divide by NULLIF(d, 0): every engine then answers NULL when d is 0.

2 problems: 1 error, 1 warning.

The example also runs into Integer division, which has a post of its own.

baresql rules division-by-zero prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.