INTEGER for whole numbers

Concepts covered: Basic Types

INTEGER stores whole numbers: 0, 42, -17, 2000000, and nothing with a fractional part. An integer literal is a bare number with no quotes. 42 is a value the database adds and compares by magnitude; '42' is a 2-character string, and the quotes are the entire difference between the 2. Integer arithmetic uses +, -, *, / and %, and an expression built from integers returns an integer, which is where the first surprise lives.

Division keeps only the whole part

The packing table records how many items went into how many boxes. Dividing 7 items across 2 boxes gives 3 per box with 1 left over, because integer division discards the remainder and % returns it. PostgreSQL and SQLite both behave this way; MySQL returns 3.5 for the same expression. Run the query and read the row for shipment 1.

SELECT shipment_id, items, boxes,
  items / boxes AS per_box,
  items % boxes AS left_over
FROM packing

Shipment 4 shows the cost of forgetting this: 100 items in 3 boxes reports 33 per box, and a report that multiplies 33 back by 3 accounts for 99 items. The fix converts one side to a type that keeps fractions, which section 4 covers with CAST. The rule to carry until then: a division between 2 integers returns an integer, and the fraction is gone before the result reaches the screen.

Aggregates follow their own rules, and those rules differ by engine. AVG(items) over the 5 shipments is 26.6, and SQLite and PostgreSQL both return the fraction, because their AVG produces a fractional type even from integer input. SQL Server returns 26 for the same call, since its AVG of an INTEGER column is an INTEGER. A query that averages an integer column and has to give the same answer on every engine casts its input first, the pattern section 4 shows.

INTEGER, BIGINT and the edge of the range

The integer types differ in how many bytes each value occupies, and the byte count fixes the largest value the column can hold.

An auto-incrementing INTEGER primary key reaches 2,147,483,647 after that many inserts, and the next insert fails with an out-of-range error rather than wrapping to a negative number. A table taking 2,000 inserts a second gets there in about 12 days. That failure has stopped production writes at companies whose event tables grew faster than their schema expected, and the repair, converting the key and every foreign key that references it to BIGINT, rewrites every row in every one of those tables. For an id column that will hold events, clicks or log lines, BIGINT costs 4 extra bytes per row and removes the question.

The ceiling applies to arithmetic as well as storage. In PostgreSQL, SELECT 2000000000 + 2000000000 fails with integer out of range, because both literals are INTEGER and so is their sum. SUM over an INTEGER column sidesteps this by returning a BIGINT. SQLite stores every integer in up to 8 bytes, so the same expression returns 4000000000 there, which means a query tested on SQLite can still fail in production on PostgreSQL.

INTEGER remains the right choice for counts, quantities, ranks and most ids, because a 4-byte value packs twice as many values into each page the database reads, and into each index it keeps, as an 8-byte BIGINT does. Money is a common integer too: storing an amount as whole cents in a column such as amount_cents, which the practice database's ledger_postings table does, keeps every total exact. The DECIMAL type, which stores fractional amounts exactly, belongs to the next tier.

Multiplying an integer column

The practice database's order_items table stores quantity as an integer and unit_price as a floating-point REAL. A line total multiplies the 2, and the result takes the wider type, since an integer combined with a fractional number is promoted to the fractional type before the arithmetic runs. Supply the operator.

A quantity of 3 at a price of 19.99 returns 59.97, because the product of an integer and a REAL is a REAL. The promotion runs in one direction only: the database widens the integer to match the fractional side and never truncates the fractional value to fit the integer, so mixing the 2 types in a division is the way to keep the fractional part. A REAL result is approximate, though, stored in binary to about 15 significant digits, so a sum over millions of line totals can drift by fractions of a cent. That drift is why money that has to reconcile is better held as integer cents.

About this lesson

This section is part of the Data Types: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.