Dividing Decimals Gives Six Decimal Places on MySQL and Sixteen on PostgreSQL

Split an invoice of 80.50 three ways and MySQL and MariaDB hand back 26.833333, PostgreSQL 26.8333333333333333 and SQLite a floating point number. Most of the time nobody notices. Then a report that adds the shares back up gets a different total on MySQL than on PostgreSQL.

MySQL and MariaDB give the result of a decimal division four more decimal places than the dividend has (the div_precision_increment setting) and cut it there. PostgreSQL keeps at least 16 significant digits, and SQLite divides as floating point numbers. Shares of an amount and averages of a DECIMAL column then differ in their last digits.

What Each Engine Answered

The example

SELECT 80.50 / 3;

gave these answers:

What to Write Instead

This form answers the same on every engine:

SELECT ROUND(80.50 / 3, 2);

Round on purpose to the places you need: ROUND(80.50 / 3, 2) is 26.83 on all four engines.

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: 80.50 / 3 is 26.833333 on MySQL and MariaDB, 26.8333333333333333 on PostgreSQL and a floating point number on SQLite. [decimal-division]
    80.50 / 3
    Round on purpose to the places you need: ROUND(80.50 / 3, 2) is 26.83 on all four engines.

1 problem: 1 warning.

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