Postgres by Example: Numeric Types

PostgreSQL offers three families of numeric types: integers, exact decimals, and floating-point. Each has its place. Integers are fast and compact for counters and IDs. numeric (also spelled decimal) gives you exact arithmetic — essential for money. Floats (real, double precision) are fast and small but can produce tiny rounding errors and should not be used for currency.

What you'll learn:

• Integer family: smallint, integer, bigint
• Exact decimals: numeric(p, s) and unconstrained numeric
• Floating-point: real, double precision
• How precision and scale work
• When each type is the right choice

-- Integer family
SELECT 1::smallint AS s, 1::integer AS i, 1000000::bigint AS b;

-- Range limits (smallint -32768..32767, integer -2.1B..2.1B, bigint very large)
SELECT pg_typeof(1) AS literal_int_type;  -- 'integer' by default

-- Exact decimal: numeric(precision, scale)
-- precision = total digits, scale = digits after the decimal point
SELECT 3.14::numeric(4, 2) AS pi, 12345.67::numeric(7, 2) AS money;

-- Unconstrained numeric stores any number with full precision
SELECT '0.1'::numeric + '0.2'::numeric AS exact;  -- 0.3, exactly

-- Floats are approximate but fast
SELECT 0.1::double precision + 0.2::double precision AS approximate;  -- 0.30000000000000004

-- Useful generators
SELECT generate_series(1, 5) AS n;

integer is your default integer type and is what 1 parses as. Switch to bigint when you might exceed two billion (rows in a large table, milliseconds since epoch, etc.). smallint is rarely worth it — the storage savings are tiny and you risk silent overflows.

numeric(p, s) enforces up to p total digits with s of them after the decimal point. numeric(10, 2) stores values from -99,999,999.99 to 99,999,999.99. Unconstrained numeric (no p, s) accepts any number with full precision — slower than fixed integers, but exact. Use it for currency, lab measurements, anything where rounding errors are unacceptable.

real (4 bytes, ~6 significant digits) and double precision (8 bytes, ~15 significant digits) are IEEE 754 floats. They are fast and small, but 0.1 + 0.2 is not exactly 0.3. Use them for physical measurements, ML features, statistical aggregations where you can tolerate the imprecision.

To run:

$ psql -f source/numeric-types.sql postgres
 s | i |    b
---+---+---------
 1 | 1 | 1000000
(1 row)
 ...
 exact
-------
 0.3
(1 row)

   approximate
------------------
 0.30000000000000004
(1 row)

Common pitfalls:

• Storing money in double precision — eventually the cent-rounding catches you. Use numeric(p, 2) (or numeric without bounds) and let the database handle exact arithmetic.
• Choosing integer for an ID column on a large table — you can hit the 2.1-billion limit. Use bigint by default for primary keys you expect to grow.
• Casting strings with non-numeric characters: '\$5'::numeric errors. Strip first, then cast.
• Dividing integers and being surprised: 7 / 2 is 3, not 3.5. Cast: 7::numeric / 2.

Tip: pg_typeof(expression) returns the inferred type — useful when you are uncertain. For example, SELECT pg_typeof(1 / 2); returns integer, while SELECT pg_typeof(1.0 / 2); returns numeric.

Try it: Create a small table with an integer id and a numeric(10, 2) price column, then insert and inspect: CREATE TEMP TABLE p(id integer, price numeric(10,2)); INSERT INTO p VALUES (1, 9.99), (2, 1234.5); SELECT * FROM p;. Then watch what happens with SELECT 0.1::real + 0.2::real;.

Source: numeric-types.sql

Next: Text Types

Home: Postgres by Example