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.

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

• ALTER 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:

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 lives separately and is not dropped on DROP TABLE;
• there is no way to forbid explicitly inserting an arbitrary id;
• there are corner cases in pg_dump.

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. So:

0.1 + 0.2 = 0.30000000000000004 (in float)

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 fiat currencies — 3). One missed divisor in the code turns into a bug.

For a typical marketplace or SaaS billing — 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.

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.

Common mistakes

id as integer — it will run out one day. Use bigint.

serial / bigserial — the deprecated way. Use GENERATED ALWAYS AS IDENTITY.

Money in float or double precision — accumulates error. Use numeric(p, s).

The money type — tied to the locale, no currency code. Use numeric(p, s).

smallint for counters — it will overflow. Use integer or bigint.

Boolean as smallint or varchar — unclear and unreliable. Use boolean.

In short

• A table's id is always bigint. Migrating int → bigint on a large table is expensive; 4 extra bytes are not.
• 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.
• An alternative for money is cents in bigint, but it is limited when currency precisions differ.
• real/double precision — acceptable for metrics, scientific data, ML. 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.