PostgreSQL NUMERIC vs DOUBLE PRECISION: Which Should You Use?

See exact and approximate arithmetic side by side

select
  0.1::numeric + 0.2::numeric as exact_sum,
  0.1::double precision + 0.2::double precision as approximate_sum,
  (0.1::double precision + 0.2::double precision) = 0.3::double precision
    as floats_are_equal;

The exact sum is 0.3. The floating-point equality can be false because these decimal fractions do not have exact binary representations.

Choose by what an error means

A price of 19.99, a tax rate, and an account balance usually have decimal business rules. Store them as NUMERIC with an intentional scale, such as NUMERIC(12,2) for an amount that may have ten digits before the decimal point and two after it.

A latitude, sensor reading, model score, or physical measurement already has limited accuracy. DOUBLE PRECISION usually fits that model better. It offers a wide exponent range and fixed eight-byte storage, but calculations can produce a nearby value instead of the decimal value you typed.

Set precision and scale deliberately

NUMERIC(precision, scale) constrains stored values. Precision is the total count of significant digits and scale is the number of fractional digits. PostgreSQL rounds to the declared scale, then rejects a value when the digits before the decimal point exceed the remaining capacity.

Unconstrained NUMERIC accepts values of different scales up to PostgreSQL's implementation limits. That flexibility is useful for calculations, but a business column normally benefits from a declared scale or a separate CHECK constraint that states the domain rule.

Constrain a decimal amount

create table invoices (
  id bigint generated always as identity primary key,
  subtotal numeric(12,2) not null check (subtotal >= 0),
  tax_rate numeric(7,6) not null check (tax_rate between 0 and 1)
);

insert into invoices (subtotal, tax_rate)
values (149.95, 0.082500);

Avoid exact equality for floating-point results

Two DOUBLE PRECISION calculations that should mean the same thing can differ in their last bits. Compare within a tolerance chosen from the domain rather than using equality on a computed result. The tolerance must have a real unit; a universal epsilon is not a substitute for understanding the data.

Compare approximate values with a domain tolerance

select abs(measured_value - expected_value) <= 0.000001 as close_enough
from experiment_results;

This tolerance is only an example. Choose one that matches the measurement resolution and the decisions made from it.

Know what FLOAT means in PostgreSQL

REAL and DOUBLE PRECISION are PostgreSQL's direct floating-point types. The SQL spelling FLOAT(p) uses p as minimum binary precision: FLOAT(1) through FLOAT(24) selects REAL, and FLOAT(25) through FLOAT(53) selects DOUBLE PRECISION. FLOAT without p selects DOUBLE PRECISION.

Use the explicit REAL or DOUBLE PRECISION name in schemas when you want the type to be obvious during review. A cast can make literal and expression behavior clear when exact and approximate values meet in one calculation.

Inspect the selected PostgreSQL types

select
  pg_typeof(1.25::float) as bare_float,
  pg_typeof(1.25::float(24)) as float_24,
  pg_typeof(1.25::float(53)) as float_53;

Test rounding and special values

NUMERIC and floating-point types can differ when a value lands exactly on a rounding tie. PostgreSQL NUMERIC rounds ties away from zero, while floating-point rounding depends on the platform and commonly rounds to the nearest even number.

PostgreSQL also supports Infinity, -Infinity, and NaN for floating types and unconstrained NUMERIC. Test how your driver serializes them before allowing those values in an application column. For other schema decisions, use the numeric type reference and the PostgreSQL SQL cheatsheet.