Decision: choose the right data type

Choosing a data type is one of the most expensive decisions to fix later: the wrong type produces wrong results (e.g. float for money), wasted space and slow queries. This tree guides you to the right type for numeric, text, date/time, key (PK), semi-structured (JSON) and category (ENUM) fields, and highlights classic pitfalls such as money and character sets.

What kind of data are you storing?

The right type is determined by the nature of the data. Which category does the field fall into: a number, text, a date/time, the table's primary key, flexible/semi-structured (JSON) data, or a limited set of categories?

• Numeric value (quantity, money, measurement, counter)
• Text (name, description, code)
• Date and/or time
• I am designing a primary key (PK)
• Flexible/nested/variable fields (semi-structured data)
• A limited, known set of categories (status, type, priority)

Is the number an integer or a decimal — and is it money?

For numerics, the most critical distinction is exactness. Is the value an integer (quantity, ID, counter) or a decimal? If decimal, is it MONEY/a financial amount? Binary floating point (FLOAT/DOUBLE) is never used for money or anywhere exactness is required, because it produces rounding errors.

• Integer (quantity, counter, id)
• Money or exact decimal (price, balance, rate)

Recommendation: integer (SMALLINT / INT / BIGINT)

For integers, pick the smallest type that covers the required range. An unnecessarily large type wastes space and index size; but for fields that can grow, such as counters/PKs, sufficient width is essential to prevent overflow.

Recommendation: NUMERIC / DECIMAL — NEVER FLOAT for money

For money and exact decimals use fixed-point (exact) NUMERIC/DECIMAL. FLOAT/DOUBLE are binary floating point and cannot store values like 0.1 exactly; addition/multiplication produces cent drift, wrong balances and audit problems.

Recommendation: text — VARCHAR vs TEXT vs CHAR

For text, VARCHAR/TEXT is used for variable length and CHAR for truly fixed length. In modern engines the storage difference between VARCHAR and TEXT is mostly negligible; the real decision is whether to impose a semantic limit (a length constraint).

Recommendation: date/time — TIMESTAMP with time zone

If you store an instant (an event time), choose a time-zone-aware type. Storing without a time zone leads to wrong calculations and comparisons across regions/daylight saving changes.

Recommendation: primary key — BIGINT vs UUID

There are two main options for a PK: an increasing number (BIGINT identity/sequence) or a UUID. A numeric PK is small and index-friendly but predictable and requires central generation; a UUID can be generated in a distributed way but is larger and its random order can hurt the index.

Recommendation: JSON / JSONB — use in moderation

The JSON type is powerful for fields with a flexible schema, nested or sparse fields; but relational discipline (constraints, indexes, type safety) can be lost. Rule: move fixed and frequently queried fields into normal columns, keep only the truly variable part in JSON.

Recommendation: ENUM vs lookup (reference) table

There are two options for limited category sets: the engine's ENUM type, or a lookup table + foreign key. ENUM is compact and simple, but adding a value requires a schema change; a lookup table is flexible and can carry extra attributes (description, active flag, order).