Choosing column types · SQL types and the type system

Choosing column types

ConceptFoundation3 min read10 min hands-on#fundamentals#sql#data-modeling

The idea in 30 seconds

A column's type decides what values it accepts and how arithmetic on them behaves. Most type mistakes are silent: floating-point money that drifts by fractions of a paisa, an integer column that overflows at about 2.1 billion, a phone number that loses its leading zero. Pick the type that stores the value exactly as the business means it.

Helps to know first: SQL data modification

Why the type matters

A type is a promise about every value in a column: that it is a whole number, or a date, or text of at most 20 characters. PostgreSQL enforces the promise on every write and uses it to decide what +, < and sum mean. Choose a type that is too loose and bad data gets in; choose one that does not match the meaning and the arithmetic quietly goes wrong.

Numbers

Type	Holds	Use for
`integer`	Whole numbers from −2,147,483,648 to +2,147,483,647	Counts, quantities, small ids
`bigint`	Whole numbers up to about 9.2 × 10¹⁸	Ids of tables that may grow large, big counters
`numeric(p, s)`	Exact decimals	Money, and anything that must add up exactly
`double precision`	Approximate decimals (IEEE 754)	Measurements and scientific values where tiny errors are acceptable

PredictAdding up ten payments of 0.10

Ten customers each pay 0.10. You store the amounts as double precision and add them up with sum. What total does PostgreSQL report?

0.9999999999999999. We ran it:

SELECT sum(x) FROM (SELECT 0.1::double precision AS x FROM generate_series(1, 10)) t;
-- 0.9999999999999999
SELECT sum(x) FROM (SELECT 0.1::numeric AS x FROM generate_series(1, 10)) t;
-- 1.0

double precision stores numbers in binary, and 0.1 has no exact binary representation, just as 1/3 has no exact decimal one. Each value is a tiny bit off, and the errors add up. Even 0.1 + 0.2 = 0.3 is false in floating point. numeric stores decimal digits exactly, which is why it is the type for money.

Integers have a hard ceiling:

SELECT 2147483647::integer + 1;

ERROR:  integer out of range

An integer id column for a busy table, such as events, log lines or order items, can reach that limit, and every insert after it fails. Use bigint for ids that might ever get there.

Text

PostgreSQL has three text types: text, varchar(n) and char(n). Unlike some other databases, they perform the same; the only differences are the length check and padding. That leaves one real question: is there a genuine maximum length? If yes, varchar(n) enforces it; if not, use text.

CREATE TEMP TABLE t (city varchar(5));
INSERT INTO t VALUES ('Mumbai');

ERROR:  value too long for type character varying(5)

Values that only look like numbers

Phone numbers, postcodes and account numbers are made of digits, but they are identifiers, not quantities: you never add two phone numbers. Store them as text.

SELECT '0042'::integer;            -- 42: the leading zeros are gone
SELECT '+91 98765 43210'::bigint;  -- ERROR: invalid input syntax for type bigint

Check yourself

Why should money be stored as numeric, not double precision?
Show answer
double precision is approximate: values like 0.1 cannot be stored exactly, so totals drift, and equality checks such as 0.1 + 0.2 = 0.3 fail. numeric stores decimal values exactly.
I could explain this without looking
When does the choice between integer and bigint matter?
Show answer
When a column might ever exceed about 2.1 billion. Past integer’s maximum, inserts fail with “integer out of range”, so busy tables’ ids and large counters should be bigint.
I could explain this without looking
Is varchar(255) faster than text in PostgreSQL?
Show answer
No. They perform the same; varchar(n) only adds a length check. Use it when there is a real maximum length, and text otherwise.
I could explain this without looking
Why store a phone number as text?
Show answer
It is an identifier, not a quantity. Number types drop leading zeros and reject characters like + and spaces.
I could explain this without looking

Sources

Where each claim on this page comes from.

PostgreSQL documentation: 8.1 Numeric Types

Primary· §8.1 Numeric Types, Table 8.2

Ranges of smallint, integer and bigint; numeric is exact and recommended for money; real and double precision are inexact IEEE 754 types.

PostgreSQL documentation: 8.3 Character Types

Primary· §8.3 Character Types

text, varchar(n) and char(n) perform the same in PostgreSQL apart from length checks and padding; storing an over-length value is an error, but an explicit cast truncates silently.