Right, let’s talk about floating-point numbers. You’ve probably heard the horror stories: “never use floats for money,” “0.1 + 0.2 != 0.3,” and so on. It’s not that they’re evil; they’re just deeply, fundamentally misunderstood. They’re a tool, and like any powerful tool, you can shoot your own foot off spectacularly if you don’t know how it works. I’m here to make sure you keep all your toes.

The root of all this chaos is a brilliant, clever, and occasionally infuriating standard called IEEE 754. Think of it as a committee’s best attempt to represent the infinite set of real numbers in a finite amount of memory. It’s a compromise, and like all compromises, it makes nobody perfectly happy but is better than the alternatives (like everyone inventing their own, even worse, formats).

How IEEE 754 Actually Works (The Magic Trick)

Forget base-10 for a moment. IEEE 754 represents numbers in binary scientific notation. A real (or float, typically 32-bit) number is packed into three parts:

  1. The Sign Bit: One bit. 0 for positive, 1 for negative. Simple.
  2. The Exponent: 8 bits. This represents the power of 2 to multiply by. It’s stored in “biased” form (127 for float) to allow for negative exponents without a separate sign bit. Clever.
  3. The Significand (or Mantissa): 23 bits. This represents the significant digits of the number itself. There’s a hidden leading 1. implied for normalized numbers, which gives you an extra bit of precision for free. Very clever.

A double precision (double, 64-bit) number just gives you more of everything: an 11-bit exponent (biased by 1023) and a 52-bit significand. This is why it’s both more precise and can represent a wider range of numbers.

So the value is basically: (-1)^sign * 2^(exponent - bias) * 1.significand

This design is why some numbers you think should be simple, aren’t. Let’s see the classic example in action.

SELECT 0.1::float + 0.2::float AS "The Horror";

 The Horror
------------
        0.3

…wait, that can’t be right. Let’s look closer.

SELECT 0.1::float + 0.2::float = 0.3::float AS "Equality Check";

 Equality Check
----------------
 f

Ah, there it is. The lie is in the display. PostgreSQL, trying to be helpful, rounds the displayed result. But underneath, it’s not exactly 0.3. Let’s see what it actually is.

SELECT 0.1::float + 0.2::float AS result,
       to_hex(0.1::float) AS hex_0_1,
       to_hex(0.2::float) AS hex_0_2;

The exact values in hex reveal the binary representation. The decimal numbers 0.1 and 0.2 are repeating decimals in binary, just like 1/3 is in base-10. They can’t be represented exactly, so they are rounded to the nearest value that can be represented in the 24 bits of a float significand. This tiny rounding error propagates into your arithmetic.

When Precision Absolutely Matters (The Escape Hatch)

This is why you hear “never use float for money.” It’s not a suggestion; it’s a commandment. The solution? Exact numeric types. In PostgreSQL, this is numeric (or decimal). It stores numbers as… well, as numbers. No binary scientific notation tricks. It’s slower and takes up more space, but it’s precise.

-- The right way
SELECT 0.1::numeric + 0.2::numeric = 0.3::numeric AS "Sanity Restored";

 Sanity Restored
-----------------
 t

Use numeric for:

  • Financial calculations (money, percentages, shares).
  • Any case where exact decimal representation is critical and rounding errors are unacceptable.
  • Calculations where you need more than 15-16 significant digits (the limit of a double).

Use float/double for:

  • Scientific data (sensor readings, temperatures, physical constants).
  • Mathematical operations where speed and range are more important than perfect decimal precision (e.g., sqrt(), log()).
  • Any time you’re dealing with values where “about right” is genuinely good enough.

The Perils of NaNs and Infinity

Here’s a fun IEEE 754 feature you didn’t ask for but definitely got: it has built-in concepts for “Not a Number” (NaN) and infinity. This is both a blessing and a curse. It allows operations to continue instead of crashing when you do something impossible, like taking the square root of a negative number.

SELECT 'NaN'::float, 'Infinity'::float, '-Infinity'::float;

But they have… interesting… behaviors.

SELECT 'NaN'::float = 'NaN'::float; -- Is NaN equal to itself? Nope.

 ?column?
----------
 f

This is the correct behavior according to the standard, but it breaks intuitive assumptions about equality. You must use isnan() or other specific functions to check for them.

The Takeaway: Know Your Domain

The choice isn’t between “good” and “bad” types; it’s between “appropriate” and “inappropriate.” Understand the inherent trade-off: float and double are fast and have a huge range but introduce controlled, predictable rounding errors. numeric is exact but slower and more cumbersome.

Your job is to match the type to the domain of your problem. If you’re calculating a rocket’s trajectory, double is your friend. If you’re calculating an invoice, numeric is your only choice. Anything else is just willingly introducing a bug and hoping nobody notices. Don’t be that person.