Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 90 ยท Data Types

Numeric Types

Pick the right PostgreSQL number type: smallint, integer, bigint, exact numeric(p,s), real and double precision, why floats surprise you, and why to avoid money.

Numbers look simple until the first invoice total is off by a cent or an id column hits two billion. PostgreSQL gives you several numeric types, and choosing between them is one of the first real design decisions in any schema. The choice affects correctness (exact versus approximate), capacity (how large a value fits) and speed.

This lesson covers the integer types, exact numeric, the floating-point types, and the money type you should usually avoid. PostgreSQL errors and column definitions are shown as read-only blocks. The browser's SQLite engine has a simpler number model, so the live examples demonstrate the same underlying ideas: why floating-point sums drift, how integer division works, what happens at the edge of the integer range, and how to store money exactly with whole cents.

The numeric family

Integers smallint, integer, bigint. Whole numbers, fast and exact.
numeric Exact decimals with a chosen precision and scale.
Floats real and double precision. Fast, approximate.
money Locale-dependent. Usually the wrong choice.

Rule of thumb: whole numbers as integers, money as numeric, measurements as floats.

1 / 2

Integer types

Three types store whole numbers. They differ only in size and range:

Type Storage Range Alias
smallint 2 bytes -32,768 to 32,767 int2
integer 4 bytes -2,147,483,648 to 2,147,483,647 int, int4
bigint 8 bytes about ยฑ9.2 ร— 10^18 int8

integer is the sensible default for counts, quantities and years. Use smallint only when you know the values are tiny and you have a lot of rows. Use bigint for anything that can grow without a ceiling: primary keys on big tables, event counters, byte sizes, epoch timestamps in microseconds.

Two billion sounds like a lot until a busy table gets there, and when an integer key overflows, the fix is a painful rewrite of a huge table. If a table could ever have hundreds of millions of rows, start with bigint. Exceeding a type's range is an error, not a silent wrap-around:

SQL
-- PostgreSQL
SELECT 2147483647 + 1;
-- ERROR:  integer out of range

SELECT 2147483647::bigint + 1;
-- 2147483648

Integer arithmetic keeps the integer type, and integer division truncates toward zero:

SQL
-- PostgreSQL
SELECT 7 / 2, -7 / 2, 7 % 2;
--  ?column? | ?column? | ?column?
-- ----------+----------+----------
--         3 |       -3 |        1

SELECT 1 / 0;
-- ERROR:  division by zero

Notice the error on division by zero: PostgreSQL does not return NULL or infinity for integers. To get a fractional result, make one side a decimal: 7 / 2.0 is 3.5. Aggregates widen their results so they do not overflow too easily: sum of an integer column returns bigint, sum of a bigint returns numeric, and avg of integers returns numeric. Auto-numbering columns (serial, identity columns) are the topic of UUIDs, Serials and Identity Columns.

SQLite stores integers in up to 8 bytes, the same range as bigint, but when an integer calculation overflows it quietly switches to a floating-point value instead of raising an error. The next example shows that and the division rules:

SQL Integer division and the edge of the range

Press Run (or Ctrl + Enter) to see the result here.

The first row shows the same truncating division as PostgreSQL (3, -3), and mixing in a decimal makes the result fractional. The second row ends with one_past_it being real: SQLite gave up on exactness without telling you. PostgreSQL would have refused, which is usually what you want.

numeric: exact decimals

numeric (also spelled decimal) stores decimal numbers exactly. You declare it with a precision (the total number of significant digits) and a scale (how many of those are after the decimal point):

SQL
-- PostgreSQL
CREATE TABLE invoices (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  net     numeric(10, 2) NOT NULL,   -- up to 8 digits before the point, exactly 2 after
  vat     numeric(10, 2) NOT NULL,
  rate    numeric(5, 4)  NOT NULL    -- for example 0.2000
);

numeric(10, 2) holds values from -99,999,999.99 to 99,999,999.99. Extra decimals are rounded to the scale when you store them, but values too big for the precision are rejected:

SQL
-- PostgreSQL
SELECT 123.456::numeric(5, 2);
-- 123.46

SELECT 1234.5::numeric(5, 2);
-- ERROR:  numeric field overflow
-- DETAIL:  A field with precision 5, scale 2 must round to an absolute value less than 10^3.

The limits are generous. A declared numeric(p, s) allows a precision p up to 1000 (numeric(1001, 0) fails with "NUMERIC precision 1001 must be between 1 and 1000"), while an unconstrained numeric can hold up to 131,072 digits before the decimal point and 16,383 after it. The scale may even be negative, which rounds to the left of the point: 12345::numeric(5, -2) stores 12300. numeric also has a special NaN value, and scale(1.500) returns 3, the number of digits after the point.

Plain numeric with no arguments accepts any precision (up to a very large limit), which is flexible but gives you no validation. For money, rates and anything where every digit counts, numeric(p, s) is the right type, as it behaves just like the arithmetic you do by hand:

SQL
-- PostgreSQL
SELECT 0.1 + 0.2 = 0.3;
-- t   (decimal literals are numeric, so the sum is exact)

The cost is speed and size. numeric is a variable-length type that takes roughly two bytes per four decimal digits plus a small header, and its arithmetic is done in software, so it is slower than integers or floats. For accounting that trade is nearly always worth it; for a million sensor readings per second it is not.

real and double precision: approximate numbers

real (4 bytes, about 6 decimal digits of precision) and double precision (8 bytes, about 15 digits) follow the IEEE 754 floating-point standard, exactly like float and double in most programming languages. They are fast, compact, and have an enormous range, but they store values in binary, and many decimal fractions such as 0.1 have no exact binary form. Tiny errors appear and can accumulate:

SQL
-- PostgreSQL
SELECT 0.1::double precision + 0.2::double precision AS sum,
       0.1::double precision + 0.2::double precision = 0.3 AS equal;
--          sum          | equal
-- ----------------------+-------
--  0.30000000000000004  | f

The same surprise happens in SQLite, which uses the same 8-byte format for its real numbers, so you can reproduce it live:

SQL Why floats drift

Press Run (or Ctrl + Enter) to see the result here.

Ten tenths do not add up to one, and at the scale of 10^16 adding 1 changes nothing because the number has run out of digits. Three habits follow:

  1. Never compare floats with =. Compare their difference to a small tolerance, as the close_enough column does.
  2. Never use floats for money, counters or anything that must add up exactly.
  3. Use floats for measurements: temperatures, coordinates, statistics, scientific data, where the inputs are already approximate and speed matters.

Floats also have special values: Infinity, -Infinity and NaN ("not a number"). Unlike IEEE hardware, PostgreSQL treats NaN = NaN as true and sorts NaN after every other value, so that indexes and sorting work.

Casting and rounding

Converting between types follows rules that are worth knowing. Casting a numeric to an integer rounds half away from zero, while casting a double precision rounds half to even on most platforms:

SQL
-- PostgreSQL
SELECT 2.5::numeric::integer          AS from_numeric,
       2.5::double precision::integer AS from_float,
       round(2.5)                     AS round_numeric,
       round(2.5::double precision)   AS round_float;
--  from_numeric | from_float | round_numeric | round_float
-- --------------+------------+---------------+-------------
--             3 |          2 |             3 |           2

When you need predictable rounding for money, keep the values in numeric all the way through and round once, at the end. The SQL numeric functions lesson covers round, ceil and floor.

Types of literals follow simple rules too. A whole number literal is an integer, or a bigint if it is too large for that; a literal with a decimal point is numeric; and one with an exponent (1.5e3) is double precision. You can check with pg_typeof:

SQL
-- PostgreSQL
SELECT pg_typeof(1), pg_typeof(3000000000), pg_typeof(1.5), pg_typeof(1.5e0);
--  pg_typeof | pg_typeof | pg_typeof |    pg_typeof
-- -----------+-----------+-----------+------------------
--  integer   | bigint    | numeric   | double precision

The money type

PostgreSQL has a type called money, and it is tempting. It prints like $1,234.56 and supports arithmetic. But it has real drawbacks: its format and number of decimal places depend on the server's lc_monetary setting, it stores no currency (is that 100 dollars or 100 yen?), it is less precise when you divide or multiply, and values copied between databases with different settings can change meaning. The PostgreSQL documentation itself recommends numeric for most uses. Store the amount in numeric(12, 2) and the currency code in a separate char(3) column.

Money the portable way: whole cents

Another exact way to store money, often used with SQLite and with APIs, is to keep integer cents. Integers are exact, small and fast, and you convert to dollars only for display. The next example compares a float price with the cents approach on the bookstore data:

SQL Storing prices as integer cents ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

The conversion uses round before CAST, because a float such as 14.99 times 100 can land at 1498.9999999999998 and truncate to the wrong cent. In PostgreSQL you would write numeric columns and skip the conversion, but the cents idea is still the standard answer in payment APIs.

Choosing a numeric type

You are storing Use
Counts, quantities, years, small whole numbers integer
Primary keys, counters that can grow, sizes in bytes bigint
Tiny bounded values on huge tables smallint
Prices, taxes, balances, rates numeric(p, s)
Measurements, coordinates, statistics double precision
Anything exact that you may divide later numeric (without scale)

When in doubt, prefer exact types. You can always convert an exact value to a float later, but you cannot get lost digits back.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What happens in PostgreSQL when SELECT 2147483647 + 1; is executed with plain integer literals?

    PostgreSQL raises an error instead of wrapping around. Casting one operand to bigint, as in 2147483647::bigint + 1, gives 2147483648.
  2. 2. A column is declared numeric(5, 2). Which value can it store?

    numeric(5, 2) allows 3 digits before the point and 2 after. Extra decimals are rounded, but a value needing more integer digits fails with "numeric field overflow".
  3. 3. Why should you not use double precision for prices?

    Values such as 0.1 have no exact binary form, so sums can drift (0.1 + 0.2 is not exactly 0.3). Use numeric or integer cents for money.
  4. 4. What does 7 / 2 return when both operands are integers?

    Integer division truncates toward zero, so the result is 3. Use 7 / 2.0 or 7::numeric / 2 to get 3.5.
  5. 5. Which is the best type for a primary key on a table that may grow to billions of rows?

    bigint holds values up to about 9.2 quintillion. An integer key tops out near 2.1 billion, and replacing it later on a huge table is painful.

Summary

  • smallint, integer and bigint are exact whole-number types of 2, 4 and 8 bytes; exceeding the range is an error, and integer division truncates.
  • Use bigint for keys and counters that may grow large.
  • numeric(p, s) is exact and the right choice for money and rates; it is slower and larger than integers or floats.
  • real and double precision are fast IEEE 754 floats whose binary storage causes tiny errors: never compare them with = and never use them for money.
  • Casting numeric to integer rounds half away from zero; casting a float rounds half to even.
  • Avoid the money type: it depends on locale and stores no currency. Use numeric(12, 2) plus a currency column, or integer cents.
  • When unsure, pick the exact type.

Next: Text Types and Collations, where you decide between text, varchar and char.