Menu

SQLite REAL: Floating-Point Storage, Precision, and Examples

SQLite REAL is used for floating-point values. SQLite stores values in the REAL storage class as 8-byte IEEE 754 binary64 floating-point numbers.

The important consequence is that REAL values are approximate, not exact decimal numbers.

SQLite REAL Example

CREATE TABLE measurements (
  id INTEGER PRIMARY KEY,
  temperature REAL
);

INSERT INTO measurements (temperature)
VALUES (21.75);

SELECT temperature, typeof(temperature)
FROM measurements;

A typical result is:

21.75|real

REAL Is an 8-Byte Floating-Point Value

SQLite does not have separate FLOAT and DOUBLE storage classes.

A value stored as REAL uses IEEE 754 binary64, the format commonly called double precision.

Values such as 0.1 usually cannot be represented exactly in binary floating point. This behavior is normal for binary floating-point arithmetic and is not specific to SQLite.

REAL Type Affinity

SQLite uses dynamic typing. The declared type of a column determines its type affinity, but it does not rigidly restrict every stored value to one data type.

A column declared as:

value REAL

has REAL affinity.

You can inspect a value’s storage class with typeof():

SELECT typeof(1.5);   -- real
SELECT typeof(1);     -- integer

REAL vs INTEGER

Use INTEGER when the value is an exact whole number and fits in SQLite’s signed 64-bit integer range.

Use REAL when fractional values or floating-point range are more important than exact decimal representation.

Typical uses include:

  • scientific measurements
  • geographic coordinates
  • temperatures
  • ratios and percentages
  • calculated values where small floating-point error is acceptable

REAL vs DECIMAL in SQLite

Declaring a column as DECIMAL does not give SQLite a native fixed-point decimal storage class.

A declaration such as:

price DECIMAL(10,2)

normally gives the column NUMERIC affinity. SQLite can still store values using its standard storage classes, including INTEGER and REAL.

So DECIMAL(10,2) in SQLite does not behave like a fixed-precision decimal type in databases such as MySQL or PostgreSQL.

Should You Use REAL for Money?

Usually, do not use REAL when exact monetary arithmetic is required.

Because binary floating-point values are approximate, repeated calculations can produce rounding differences.

A common approach is to store the smallest currency unit as an integer:

CREATE TABLE product (
  id INTEGER PRIMARY KEY,
  price_cents INTEGER NOT NULL
);

INSERT INTO product VALUES (1, 1999);

Here, 1999 represents $19.99 exactly.

Summary

  • SQLite REAL values use 8-byte IEEE 754 binary64 floating point.
  • Floating-point values are approximate.
  • A REAL column has REAL affinity, but SQLite remains dynamically typed.
  • DECIMAL does not create a native exact decimal storage class.
  • Prefer integer minor units instead of REAL for exact money calculations.

See SQLite’s official Datatypes and Floating Point Numbers documentation.

Verify the example in your browser

Copy the CREATE TABLE, INSERT and SELECT statements above into the SQLite Playground, then change the stored values and compare typeof() results. The playground runs SQLite locally in the browser; it is useful for checking the actual storage class instead of inferring it from the declared column name.