REAL on PostgreSQL and FLOAT on MySQL Are 4-Byte Floats That Drop Digits

Measurements kept in a REAL column on PostgreSQL, or in a FLOAT column on MySQL or MariaDB, come back with fewer digits than went in. The same type names keep them all on the other engines.

PostgreSQL’s REAL is a 4-byte float, and so is FLOAT on MySQL and MariaDB, while the other engines store 8 bytes for the same name. 1.23456789 comes back as 1.2345679 or 1.23457.

What Each Engine Answered

The example

CREATE TABLE t (r REAL, f FLOAT);
INSERT INTO t VALUES (1.23456789, 1.23456789);
SELECT r, f FROM t;

gave these answers:

Every Form BareSQL Checks

BareSQL Portable checks 2 forms of this, and each was run on all four engines. yes means the engine ran it and the engines that ran it agreed, no means the engine refused it, and a value is what the engine answered where they disagreed.

What to Write Instead

This form answers the same on every engine:

CREATE TABLE t (r DOUBLE PRECISION, f DOUBLE PRECISION);
INSERT INTO t VALUES (1.23456789, 1.23456789);
SELECT r, f FROM t;

Use DOUBLE PRECISION, an 8-byte float 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:19: warning: REAL keeps about 7 significant digits on PostgreSQL, against 15 on SQLite, MySQL and MariaDB. [float-precision]
    REAL
    Use DOUBLE PRECISION, an 8-byte float on all four engines.
example.sql:1:27: warning: FLOAT keeps about 6 significant digits on MySQL and MariaDB, against 15 on SQLite and PostgreSQL. [float-precision]
    FLOAT
    Use DOUBLE PRECISION, an 8-byte float on all four engines.

2 problems: 2 warnings.

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