Numbers in PostgreSQL: bigint, numeric, boolean and how not to lose money

← Back to the section

PostgreSQL offers many numeric types, and the choice is not obvious. In practice, almost every mistake boils down to three scenarios: the id counter overflowed, cents were lost to rounding, a flag broke because it was stored as a number. Let's go through each in turn.

The id problem: why integer will run out one day

Picture this: you create an orders table and pick integer for the id — 2 billion rows seems like it will last a hundred years. A few years later the table grows, the id is exhausted, and you discover it during a production incident on Friday night.

The counter does not wrap around to negative numbers — the database refuses to write:

-- error: integer out of range
SELECT 2147483647::integer + 1;

Migrating integer → bigint on a live, large table is days of work:

• ALTER TABLE … ALTER COLUMN … TYPE bigint requires an exclusive lock and rewrites the whole table;
• the workarounds are complex: a new column, copying data, switching over.

So the rule is simple: a table's id is always bigint.

CREATE TABLE order_item (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id    bigint    NOT NULL,
    quantity    integer   NOT NULL CHECK (quantity > 0),
    weight_g    integer   NOT NULL
);

The three integer types and their ranges:

A query shows how much bigger that is:

live example

SELECT 2147483647::integer          AS integer_max,
       9223372036854775807::bigint  AS bigint_max,
       9223372036854775807 / 2147483647 AS bigint_is_times_bigger;

The last column is the answer: the bigint ceiling is 4,294,967,298 times higher than the integer one. The difference in row size is 4 bytes. The cost of those 4 bytes is nothing compared to the cost of the incident.

When smallint is actually appropriate

smallint makes sense only for fixed scales, where overflow is physically impossible:

day_of_week     smallint NOT NULL CHECK (day_of_week BETWEEN 1 AND 7),
timezone_offset smallint NOT NULL  -- offset in minutes from UTC

For counters, limits, balances, any "business" numbers — use only integer or bigint. smallint saves 2 bytes but does not justify the overflow risk.

GENERATED ALWAYS AS IDENTITY instead of serial

The old way to write an auto-increment id was serial or bigserial:

-- the old way — not recommended
CREATE TABLE foo (
    id bigserial PRIMARY KEY
);

serial is not a real data type but a shorthand that PostgreSQL expands into an integer with a sequence and a default value. The problems:

• the sequence is a separate object with its own privileges: you grant a user the right to write to the table, and the insert still fails because there is no privilege on the sequence;
• there is no way to forbid inserting an arbitrary id explicitly — the application can supply its own, and later the sequence starts handing out numbers that are already taken;
• there are corner cases in pg_dump where the link between the column and the sequence is not carried over the way you expect.

Since PostgreSQL 10 the standard way is GENERATED ALWAYS AS IDENTITY:

-- the modern way
CREATE TABLE foo (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

ALWAYS means PostgreSQL generates the id itself and does not allow inserting an arbitrary value. This protects against accidental conflicts.

If you do need to insert an explicit id when migrating data, there is a softer version, BY DEFAULT AS IDENTITY. It allows an explicit insert but still generates the value automatically in normal operation.

When GENERATED ALWAYS AS IDENTITY is not a fit: if you need a globally unique id without a round trip to the database — for example, for distributed services. In that case, use UUID v7.

Money and floating-point numbers: the classic trap

The most common numeric mistake in PostgreSQL is storing money in float or double precision. Let's see why that is dangerous.

A computer stores float in binary form. The problem is that most decimal fractions cannot be represented exactly in binary. For example, 0.1 in binary is an infinite fraction, and it cannot be written down in full:

live example

SELECT 0.1::double precision + 0.2::double precision AS float_sum,
       0.1::numeric(10,2) + 0.2::numeric(10,2)       AS numeric_sum,
       0.1::double precision + 0.2::double precision = 0.3::double precision AS float_equals_03;

The query returns 0.30000000000000004, 0.30 and f: in double precision the sum is not 0.3, in numeric it is.

For a single transaction the error is imperceptible. But over a year, millions of operations accumulate a discrepancy that surfaces during reconciliation with the bank — and that will be a legally incorrect calculation.

The right type for money is numeric(p, s), where p is the total number of significant digits and s is the number of digits after the decimal point. numeric stores the number exactly, as decimal digits:

amount_total      numeric(15, 2) NOT NULL,   -- up to 13 digits before the point, 2 after
exchange_rate     numeric(20, 8) NOT NULL,   -- rates: 8 digits after the point
discount_percent  numeric(5, 2)  NOT NULL CHECK (discount_percent BETWEEN 0 AND 100)

An alternative: cents in bigint

Another workable approach is to store monetary amounts in whole units of the smallest denomination:

amount_cents bigint NOT NULL CHECK (amount_cents >= 0)

Pros: faster than numeric, integer operations produce no error. Cons: awkward for systems with different precisions (cryptocurrency — 8 digits, rubles — 2, some national currencies — 3). One missed divisor in the code turns into a wrong amount.

For a typical marketplace or subscription service — numeric(p, s).

The money type: why it is not used

PostgreSQL has a built-in money type. It looks convenient, but in practice it is not used:

• it is tied to the server's global locale (the output format changes when the locale changes);
• it does not store a currency code — you cannot tell rubles from dollars;
• it is awkward to convert to other types.

For any money-related task, numeric(p, s) is better.

When float is actually appropriate

real and double precision are not forbidden — they are needed where a small error is acceptable by the nature of the data:

• monitoring metrics: CPU usage, p95 latency;
• scientific calculations: weight, temperature, distance (the input data is already approximate);
• machine learning: embeddings, numeric features.

Anywhere the numbers must add up exactly — money, loyalty points, accounting quantities — float is not a fit.

Boolean is a boolean, not a number

Another common mistake is storing a boolean flag as a number or a string:

-- a common mistake
is_active   smallint NOT NULL DEFAULT 1,
is_active   varchar(1) NOT NULL DEFAULT 'Y' CHECK (is_active IN ('Y','N')),
is_active   char(1) NOT NULL DEFAULT 'Y'

This causes problems: queries become unclear, you can accidentally write 2 instead of 1, and different parts of the code start using different conventions. Compare the three ways of storing a flag:

live example

SELECT 2::smallint = 1 AS flag_as_number,
       'Y' = 'y'       AS flag_as_text,
       'yes'::boolean  AS flag_as_boolean;

The first two columns are f: a 2 instead of a 1 and a lower-case letter instead of an upper-case one silently switch the flag off, with no error. The third is t: boolean understands yes, on, 1, and nothing else can be written into it.

PostgreSQL has a built-in boolean type — use it:

is_active   boolean NOT NULL DEFAULT true,
is_deleted  boolean NOT NULL DEFAULT false

boolean takes 1 byte, reflects the semantics exactly, and works with the AND, OR, NOT operators without conversions.

In short

• A table's id is always bigint: migrating int → bigint on a large table is expensive, 4 extra bytes are not. An integer overflow does not corrupt data quietly — the write fails with integer out of range, but you fix it under load.
• Auto-increment — GENERATED ALWAYS AS IDENTITY instead of the deprecated serial/bigserial.
• smallint — only for fixed scales (day of week, UTC offset). For counters — integer/bigint.
• Money — numeric(p, s): float accumulates error, money is tied to the locale and carries no currency code. The alternative is cents in bigint, but it is limited when currency precisions differ.
• real/double precision — acceptable for metrics, scientific data, machine learning. Not for finance.
• Flags — boolean, not smallint, not varchar('Y'/'N').

What to read next

• String types — text versus varchar, when to choose which.
• Time and time zones — timestamptz and why the time zone matters.
• UUID and identifiers — when you need a UUID instead of bigint.
• Enum and enumerations — how to store statuses and categories.