SQL Floating-Point Types

Introduction

Floating-point types are SQL numeric types designed to represent approximate values across a very wide range of magnitudes. They are useful for scientific measurements, sensor readings, simulation, statistics, coordinates, probabilities, and machine-learning features where small representation differences are acceptable.

They are not simply decimal types with more digits. Their binary representation, significant precision, comparison behavior, and product-specific names require deliberate design. Using them for exact financial amounts, identifiers, or equality-based business rules can create subtle defects.

What Is Floating Point?

Floating point represents a number using a sign, a significand, and an exponent. The exponent allows the radix point to move, or float, so the same format can represent extremely small and extremely large magnitudes.

significand x base^exponent

Most modern hardware uses binary floating-point formats related to IEEE 754, although SQL product behavior, supported types, and exceptional values still vary.

Why It Is Called Floating Point

A fixed-point decimal reserves a fixed number of places after the decimal point. Floating point instead adjusts the exponent as magnitude changes. This is similar to scientific notation:

6.02 x 10^23
1.25 x 10^-8

In binary floating point, the base is generally two rather than ten. That difference explains why many simple decimal fractions are not exactly representable.

Main SQL Floating-Point Types

The common names are REAL, FLOAT, and DOUBLE PRECISION. Their meaning is not perfectly uniform across databases.

Type conceptCommon implementationApproximate significant precisionTypical use
REALSingle precisionAbout 6 to 7 decimal digitsLower-precision measurements
DOUBLE PRECISIONDouble precisionAbout 15 to 17 decimal digitsGeneral scientific and analytical data
FLOAT(p)Dialect-dependent precisionDepends on product and pPortable concept, nonportable details

These figures describe common binary formats, not guarantees for every database declaration.

Exact and Approximate Numeric Types

Integer and exact decimal types store values exactly within their declared range and precision. Floating-point types store the nearest representable binary value.

RequirementPreferReason
Exact whole-number countINTEGER or BIGINTExact discrete values
Money or contractual rateDECIMAL(p,s)Exact decimal semantics
Wide-range measurementDOUBLE PRECISIONWide magnitude and efficient arithmetic
Approximate compact sensor valueREALLower storage and sufficient precision

Why Floating Point Is Approximate

A finite binary fraction can exactly represent values built from powers of two, such as one-half or one-quarter. Decimal fractions such as one-tenth repeat indefinitely in binary, just as one-third repeats indefinitely in decimal.

The database stores the nearest representable value. Display formatting may show 0.1, but the internal binary value can be slightly above or below that decimal number.

A Simple Approximation Example

0.1 + 0.2

In binary floating-point arithmetic, this result may not be exactly equal to the representable value chosen for decimal 0.3. Different SQL products can display rounded text that hides the residual difference, so display alone is not proof of exact storage.

Sign, Significand, and Exponent

A floating-point value contains a sign, significant bits, and an exponent. The significant bits control precision, while the exponent controls scale or magnitude. Increasing magnitude does not increase the number of significant digits.

This is why a double-precision value can represent an enormous number but may not distinguish every adjacent integer at that magnitude.

Precision Means Significant Digits

Floating-point precision concerns significant digits rather than a fixed number of decimal places. The values 1234567, 123.4567, and 0.001234567 each contain seven significant digits even though their decimal-point positions differ.

A floating column does not have fixed scale like DECIMAL(12,4). The number of trustworthy decimal places changes with magnitude.

Range Versus Precision

Range answers how large or small a magnitude can be. Precision answers how finely nearby values can be distinguished. Floating point offers broad range with limited precision.

At large magnitudes, the spacing between representable values grows. Adding a small increment to a very large value may produce no change because no representable value exists between them.

REAL

REAL commonly maps to single-precision binary floating point. It uses less storage than double precision but provides fewer significant digits and a narrower exponent range.

temperature_celsius REAL
signal_strength REAL

It is suitable only when its precision covers the instrument and business requirement. Storing more digits than the sensor can measure does not create accuracy.

DOUBLE PRECISION

DOUBLE PRECISION commonly maps to a 64-bit binary floating format with substantially more significant precision and range than REAL.

latitude DOUBLE PRECISION
longitude DOUBLE PRECISION
simulation_result DOUBLE PRECISION

It is often the safer general floating default when storage difference is acceptable, but it remains approximate.

REAL Versus DOUBLE PRECISION

Choose REAL when lower precision is demonstrably sufficient and storage or memory volume matters. Choose double precision when calculations accumulate error, values span broad magnitudes, or approximately 15 significant decimal digits are useful.

Do not choose based only on one displayed sample. Include arithmetic pipelines, aggregates, serialization, and future analytical use.

FLOAT and FLOAT(p)

SQL defines FLOAT(p) in terms of approximate precision, commonly understood as binary precision. It is not equivalent to DECIMAL(p,s), and p does not normally mean decimal places.

Product behavior differs. PostgreSQL treats lower requested precision as real and higher precision as double precision within documented ranges. SQL Server uses the precision parameter to choose between its approximate storage formats. MySQL has its own syntax and historical behavior. Always check the target database.

PostgreSQL Floating Types

PostgreSQL provides real and double precision, along with float(p) shorthand that resolves to one of those families according to requested binary precision. It also supports special values such as infinity and NaN for floating types.

SQL Server Floating Types

SQL Server provides real and float(n). real corresponds to lower precision, while float(n) selects the supported approximate storage family based on n. The declaration should be reviewed with SQL Server's documented precision ranges.

MySQL Floating Types

MySQL provides FLOAT and DOUBLE. Older display-width and decimal-place syntax should not be confused with exact decimal storage. Use DECIMAL when exact decimal behavior is required.

Oracle Floating Types

Oracle provides BINARY_FLOAT and BINARY_DOUBLE for IEEE-style binary floating arithmetic, while its SQL FLOAT is related to the NUMBER family and uses different semantics. Directly mapping type names from another database can therefore be incorrect.

SQLite REAL

SQLite's REAL storage class represents an approximate floating value, commonly using an 8-byte IEEE binary format. Dynamic typing and column affinity mean declared type names do not enforce values like rigid server-database schemas do.

Cross-Database Portability

The concepts of lower and higher approximate precision are portable, but the names are not fully portable. Before migrating, verify storage, binary precision, literal typing, exceptional values, arithmetic, output formatting, drivers, and conversion rules.

When portability matters, document the required significant precision and range rather than only the source type name.

IEEE 754 and SQL

IEEE 754 defines common binary floating formats and behavior used by processors, languages, and databases. Single precision commonly uses 32 bits, while double precision commonly uses 64 bits.

SQL products may expose additional rules for ordering, NaN, infinity, conversion, aggregation, and indexes. IEEE knowledge explains the foundation but does not replace database documentation.

Representable Values Are Discrete

Between any two adjacent representable floating values, infinitely many real numbers exist that the type cannot store. Input is rounded to one available value.

The spacing is smaller near zero and larger at high magnitudes. Precision is therefore relative to magnitude rather than a constant number of decimal places.

Machine Epsilon

Machine epsilon is a concept describing the gap between 1 and the next representable value for a floating format. It illustrates finite precision but is not automatically the right application comparison tolerance.

Business tolerance should come from measurement resolution, numerical method, and acceptable error. It may be much larger than machine epsilon.

Loss of Precision at Large Magnitudes

A common double format can exactly represent integers only up to a finite threshold. Beyond that point, not every consecutive integer is available.

Converting a large BIGINT identifier to double can change its value. Never store exact large IDs in floating-point columns or serialize them through clients that only preserve approximate numbers without a safe strategy.

DECIMAL Versus Floating Point

CharacteristicDECIMALFloating point
RepresentationExact decimal within precisionApproximate binary
ScaleDeclared or product-defined decimal scaleNo fixed decimal scale
RangeDefined by precision and product limitsVery wide magnitude range
Best forMoney and exact business valuesMeasurements and numerical analysis
Equality after calculationsPredictable within exact rulesOften requires tolerance

Why Floating Point Is Poor for Money

Money generally requires exact decimal units, reproducible rounding, and audit reconciliation. Small floating residuals can accumulate across line items, taxes, conversions, and aggregates.

-- Prefer an exact declaration for posted money
amount DECIMAL(19, 4)

Floating point can still appear in financial simulations and risk models where approximation is accepted. Final posted amounts should follow defined exact decimal and rounding rules.

Appropriate Use Cases

Good candidates include temperature, voltage, coordinates, physical simulation, statistical model features, probabilities, normalized scores, scientific constants, and values using scientific notation.

The requirement must tolerate approximation and define meaningful precision. A decimal point in sample data does not by itself justify a floating type.

Accuracy and Precision

Precision describes repeatability or representational detail. Accuracy describes closeness to the true value. A database can store many significant digits from an inaccurate sensor without improving the measurement.

Schema design should reflect sensor accuracy, calibration, uncertainty, and unit, not merely the maximum digits accepted by a type.

Scientific Notation

6.02E23
1.5E-9

Scientific notation expresses wide magnitudes compactly and often leads to approximate literal typing. Explicitly cast literals when the intended type matters because literal rules vary by database.

Floating-Point Equality

Exact equality is fragile after calculations:

-- Risky for computed approximate values
WHERE calculated_value = :expected_value

Values that are mathematically equal can differ in their final low-order bits because they followed different operation sequences or representations.

Absolute Tolerance

WHERE ABS(calculated_value - :expected_value)
      <= :absolute_tolerance

Absolute tolerance works when values share a known scale and one fixed error bound is meaningful. It can be too strict for large magnitudes or too loose for tiny values.

Relative and Combined Tolerance

Relative tolerance scales the allowed difference with magnitude:

ABS(actual_value - expected_value)
    <= relative_tolerance * ABS(expected_value)

A robust comparison often combines absolute and relative tolerance so values near zero are handled sensibly. The exact formula and thresholds must come from domain requirements.

Choosing a Tolerance

Consider instrument resolution, accepted business deviation, number of operations, expected magnitude, and algorithm stability. A tolerance copied from an unrelated system can accept incorrect results or reject valid ones.

Store or document uncertainty when measurement quality is part of the data.

Floating Arithmetic

fahrenheit = celsius * 9.0 / 5.0 + 32.0

Addition, subtraction, multiplication, division, roots, powers, and trigonometric functions can each introduce rounding. The order of operations can affect the final low-order bits even when algebra says the expressions are equivalent.

Error Accumulation

Repeated approximate operations can accumulate error. Adding a tiny value millions of times may drift from an exact mathematical total. Stable algorithms, compensated summation, higher precision, or exact decimal arithmetic may be needed.

The database may not offer specialized numerical algorithms for every aggregate, so critical analysis should validate method and implementation.

Catastrophic Cancellation

Subtracting two nearly equal floating values can eliminate leading significant digits and magnify relative error in the remainder. This is called catastrophic cancellation.

Reformulating the algorithm or retaining higher precision can reduce the problem. Rounding the final display cannot recover information already lost.

Floating Aggregates

SUM and AVG over floating input remain approximate. Addition is not perfectly associative in finite precision, so different row orders can produce slightly different results.

Parallel aggregation may combine partial sums in a different order from serial execution. Small differences across runs or plans can therefore be expected even when every input row is unchanged.

GROUP BY and DISTINCT

Grouping by a raw floating value is risky when values are expected to represent conceptual buckets. Tiny representation differences can create separate groups that display identically.

-- Prefer a defined business bucket
GROUP BY measurement_band

Alternatively, derive documented ranges or rounded buckets, understanding that rounding changes grouping semantics. DISTINCT has the same exact-representation concern.

Sorting Floating Values

Ordinary finite floating values can be sorted numerically, but near-equal values may appear in an unexpected order because their stored approximations differ. NaN and infinity ordering is database-specific.

Add deterministic secondary keys when stable display order matters.

NaN and Infinity

Some products support not-a-number, positive infinity, and negative infinity for floating types. They may arise from explicit input or certain operations.

Equality, ordering, aggregates, indexes, and client serialization of these values vary. If the domain does not permit them, reject them explicitly rather than assuming only finite values will be stored.

Negative Zero

IEEE-style formats can contain positive and negative zero. They normally compare equal, but the sign can affect formatting or functions such as reciprocal and sign-sensitive numerical operations.

Database normalization and display behavior vary. Test it only if the distinction matters to the domain.

NULL Versus NaN

NULL means missing, unknown, or not applicable. NaN is a present floating value representing an undefined numerical result. Zero is a known number. These states are not interchangeable.

COALESCE(value, 0) should be used only when missing truly means zero; it does not address NaN.

Floating-Type Conversion

CAST(exact_amount AS DOUBLE PRECISION)

CAST(approximate_value AS DECIMAL(20, 8))

Converting exact decimal to floating point can lose decimal exactness. Converting floating point to decimal records a rounded decimal representation of the already approximate value. It cannot recover information lost earlier.

Integer-to-Floating Conversion

Small integers commonly convert exactly, but sufficiently large integers cannot all be represented by a floating format. Converting a BIGINT ID or counter can merge distinct values.

Keep identifiers and exact counts in integer types through database, driver, analytics pipeline, and API.

Floating Point in Joins and Keys

A floating column is usually a poor primary or foreign key because exact equality is central to key matching. Values calculated independently may be conceptually equal but have different representations.

-- Fragile design
ON a.measured_value = b.measured_value

Join on stable identifiers or defined buckets. If tolerance matching is required, treat it as a range problem and handle possible multiple matches explicitly.

Indexes on Floating Columns

A database can index floating columns, supporting ordering and exact range searches according to its type rules. Index usefulness depends on query selectivity and the predicate.

WHERE measured_value >= :minimum_value
  AND measured_value < :maximum_value

Exact equality searches can miss values that differ by tiny calculation errors. Tolerance predicates may create wider ranges and require careful performance testing.

ROUND Does Not Make Floating Point Exact

ROUND can produce a value intended for a certain display or comparison scale, but the rounded result may still be stored in an approximate binary format.

If exact decimal semantics are required, convert to an appropriate exact decimal type with a defined rounding rule. Do not apply rounding indiscriminately to hide unexplained errors.

Display Formatting Can Hide Approximation

Database clients often print only a limited number of digits. Two displayed values can look identical while their stored bits differ. Conversely, a client may expose a long decimal expansion that is only the text rendering of a nearby binary value.

Inspect type, full precision, source operations, and raw transport before diagnosing from screenshots or formatted reports.

Performance and Storage

Floating operations are generally supported efficiently by hardware. REAL commonly uses less storage and memory bandwidth than double precision, which can matter for very large analytical arrays or tables.

Performance is not a reason to use floating point when exactness is required. Storage savings should be measured against accuracy, downstream conversion, index behavior, and debugging cost.

Mathematical Functions

SQRT(measured_value)
POWER(base_value, exponent_value)
SIN(angle_radians)
LOG(sample_value)

Roots, powers, logarithms, and trigonometric functions naturally return approximate values. Validate function domains, units, overflow, NaN, and infinity behavior in the target database.

Probabilities and Model Scores

Probabilities and machine-learning scores are often floating point because models produce approximate continuous values. Add constraints if the intended range is bounded:

probability DOUBLE PRECISION
    CHECK (probability BETWEEN 0.0 AND 1.0)

Consider whether NaN passes or interacts with the constraint under product-specific rules. Validate model outputs before storage.

Mixed Exact and Approximate Schemas

A good table can use different numeric families for different meanings:

transaction_amount DECIMAL(19, 4),
risk_score DOUBLE PRECISION,
model_version INTEGER

The amount is exact, the score is approximate, and the version is a discrete whole number. Consistency means choosing each type consistently for its domain, not forcing all numeric columns into one type.

Application Mapping

Java commonly maps lower precision to float and double precision to double. Python commonly maps both to its binary float. JavaScript Number uses double-precision binary floating point.

Exact monetary values should use Java BigDecimal, Python Decimal, or equivalent decimal libraries instead. Driver and ORM behavior must be verified rather than inferred from language names.

JSON and Serialization

JSON has a general number syntax but does not guarantee binary precision or exceptional-value support. Standard JSON does not represent NaN or infinity as ordinary numbers.

Serialization can change textual digits, reject special values, or parse numbers into a different type. Define acceptable range, precision, and exceptional-value behavior in the API contract.

CSV and ETL Pipelines

CSV stores text, so import tools must parse every value. Locale-specific decimal separators, scientific notation, missing markers, and excessive digits can change results or fail loading.

Repeated conversion between floating text, binary formats, and decimals can introduce additional rounding. Preserve source metadata and validate representative edge values in ETL tests.

Migration Between Floating and Decimal

A float-to-decimal migration requires choosing target precision, scale, and rounding. Existing approximate values may reveal unexpected residual digits or exceed the target range.

A decimal-to-float migration is potentially lossy and should be justified by a domain change. Profile values, define accepted error, validate aggregates, update application mappings, and retain a rollback path.

Query Parameters

Bind parameters using types compatible with the column. Sending decimal text for a floating column or a floating parameter for an exact decimal column can cause implicit conversion, different comparison behavior, or reduced index use.

Parameter values also need validation for NaN, infinity, range, and units before reaching SQL.

Common Floating-Point Mistakes

  • Using floating point for posted money, tax, or exact contractual values.
  • Comparing calculated values with exact equality.
  • Assuming more displayed decimal places mean more accuracy.
  • Treating double precision as exact.
  • Thinking FLOAT(p) defines decimal places.
  • Using floating values as primary or foreign keys.
  • Assuming REAL and FLOAT mean the same thing everywhere.
  • Converting exact values to floating point unnecessarily.
  • Storing percentages without documenting whether 15% means 15 or 0.15.
  • Choosing floating point merely because sample data has a decimal point.
  • Ignoring NaN, infinity, negative zero, and NULL behavior.
  • Applying ROUND without understanding the source error.

Floating-Type Decision Process

  1. Determine whether approximation is acceptable.
  2. Define required significant precision and magnitude range.
  3. Identify equality, grouping, joining, and aggregation requirements.
  4. Define absolute or relative tolerance where comparison is needed.
  5. Choose lower or higher floating precision based on measured need.
  6. Confirm database-specific type semantics and special values.
  7. Verify driver, ORM, language, JSON, and ETL mappings.
  8. Add business constraints for range and finite values where possible.
  9. Test numerical stability and representative operation sequences.
  10. Use an exact type instead if any exact business requirement remains.

Example Sensor Table

CREATE TABLE sensor_readings (
    reading_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sensor_id BIGINT NOT NULL,
    measured_value DOUBLE PRECISION NOT NULL,
    uncertainty DOUBLE PRECISION,
    recorded_at TIMESTAMP NOT NULL,
    CHECK (uncertainty IS NULL OR uncertainty >= 0)
);

The value and uncertainty are approximate measurements, while IDs remain exact integers. Unit and calibration metadata should be modeled elsewhere or included explicitly.

Example Analytics Table

CREATE TABLE model_predictions (
    prediction_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    model_version INTEGER NOT NULL,
    entity_id BIGINT NOT NULL,
    probability DOUBLE PRECISION NOT NULL,
    score DOUBLE PRECISION NOT NULL,
    CHECK (probability BETWEEN 0.0 AND 1.0)
);

Approximate model outputs are suitable floating values, while identifiers and model versions remain integers. Production design should also define finite-value validation.

Reproducibility and Deterministic Results

Floating-point queries can be correct without being bit-for-bit reproducible. A database optimizer may change a scan, join, or aggregation plan after statistics, indexes, configuration, or server versions change. Parallel workers can also combine partial sums in a different order. Because floating addition is not perfectly associative, mathematically equivalent orders may produce slightly different low-order digits. This is usually harmless for analysis, but it matters when automated tests compare exact output, reports use a result as a checksum, or downstream processing treats every textual change as a business change.

Define reproducibility according to the domain. A scientific calculation may require agreement within a relative tolerance, while a visual dashboard may only require the same rounded display value. When exact repeatability is essential, consider an exact decimal representation, a stable calculation order, a compensated summation method in the analytical layer, or storage of the authoritative calculated result. Do not assume that adding ORDER BY to the final result controls the internal order used by an aggregate.

Regression tests should compare floating results using documented tolerances and should record the database engine, version, execution mode, and input set. Test both serial and parallel plans when large aggregates are important. A tolerance must not be made so wide that it hides genuine defects; derive it from measurement resolution, expected rounding error, operation count, and business impact.

Observability and Data-Quality Controls

Production monitoring should distinguish normal numerical noise from data corruption. Useful controls include accepted ranges, finite-value checks, unit validation, distribution monitoring, and alerts for sudden changes in mean, variance, missingness, or magnitude. A sensor value of 1.0000000001 instead of 1.0 may be expected approximation, while a value of 1e300, NaN, or infinity may indicate a failed device, bad transformation, or division by zero.

Preserve raw observations when possible and store derived values separately. Include the unit, collection time, source, calibration version, and algorithm version needed to interpret each number. This makes it possible to recompute results after correcting a formula without pretending that the original reading changed. For pipelines, track counts of rejected, clamped, converted, and non-finite values rather than silently modifying them.

Alerts and service-level objectives should use the same tolerance model as the application. Comparing an observed float with an exact threshold can cause noisy state changes near the boundary. Hysteresis, minimum-duration rules, or a clearly defined inclusive range can make monitoring stable while preserving sensitivity to meaningful changes.

Debugging Floating-Point Problems

Start by identifying the declared database type, source type, parameter type, and application mapping. Display sufficient significant digits, then inspect the exact operation order and every conversion.

Compare raw inputs, intermediate values, final values, and tolerances. Reproduce in the same database and client environment because another language or formatter can hide or alter the symptom. Do not begin by adding ROUND everywhere.

Testing Checklist

  • Test zero, negative zero, positive and negative values, and NULL.
  • Test expected minimum and maximum magnitudes.
  • Test values near comparison boundaries and near zero.
  • Test absolute and relative tolerance behavior.
  • Test repeated arithmetic and large aggregates.
  • Test serial and parallel aggregation where reproducibility matters.
  • Test NaN and infinity if the database supports them.
  • Test decimal, integer, and text conversion paths.
  • Test grouping, distinctness, sorting, and index predicates.
  • Test database-to-driver-to-JSON round trips at full precision.

Interview Questions and Answers

What is a floating-point type? It is an approximate numeric representation using significant digits and an exponent to cover a wide magnitude range.

Why is floating point approximate? Many decimal fractions cannot be represented as finite binary fractions, so the nearest representable value is stored.

What is the difference between REAL and DOUBLE PRECISION? They commonly map to lower and higher binary precision, but exact behavior depends on the database.

Why is FLOAT usually wrong for money? Approximate binary arithmetic can create residual differences that conflict with exact decimal reconciliation.

How should floating values be compared? Use a domain-appropriate absolute, relative, or combined tolerance rather than exact equality after calculations.

Can DOUBLE store every BIGINT exactly? No. At large magnitudes, consecutive integers are no longer individually representable.

Why can SUM vary slightly? Floating addition is not perfectly associative, so different row or parallel combination orders can change low-order bits.

Does ROUND make a float exact? No. A rounded result stored as floating point remains an approximate binary value.

Key Takeaway

Floating-point types trade exact decimal representation for wide range, significant precision, and efficient numerical computation. They are appropriate when the domain already accepts approximation and defines meaningful error tolerance.

FLOATING-POINT TYPE CHOICE =
ACCEPTABLE APPROXIMATION + REQUIRED SIGNIFICANT DIGITS
+ MAGNITUDE RANGE + NUMERICAL AND TRANSPORT BEHAVIOR

Use exact integers and decimals for IDs, money, and contractual values. For measurements and analytics, choose precision deliberately, avoid exact equality, account for aggregate order and special values, and test the complete path from database calculations through APIs and applications.