Skip to content
Tutorials Center
My learning

Topics

Lesson 69 of 125 ยท Designing Databases

Data Types

Choose the right SQL data type for every column. Compare SQLite storage classes with PostgreSQL, MySQL and SQL Server types, and store money and dates safely.

Every column you create gets a data type: integer, text, date, decimal and so on. The type decides which values the column accepts, how much space they take, how they sort and compare, and which functions make sense on them. A well-chosen type catches bad data at the door. A poorly chosen one causes rounding errors in invoices, dates that sort in the wrong order and "numbers" that are secretly text.

Data types are also where SQL dialects differ the most. PostgreSQL, MySQL and SQL Server have dozens of strict types, while SQLite, the database running in your browser here, takes a famously relaxed approach. This lesson covers the standard type families, shows how SQLite handles them, gives you a comparison table for the big databases and finishes with the two types beginners get wrong most often: money and dates.

If you want the details of how SQLite converts values and how CAST works, see Types and CAST. Here we focus on designing columns.

The standard type families

The SQL standard groups types into a handful of families. Every major database supports some version of each.

Family Standard types Holds
Exact numbers SMALLINT, INTEGER, BIGINT, DECIMAL(p, s), NUMERIC(p, s) Counts, IDs, money
Approximate numbers REAL, DOUBLE PRECISION, FLOAT Measurements, scientific values
Text CHAR(n), VARCHAR(n), CLOB (most databases also have TEXT) Names, codes, descriptions
Binary BINARY(n), VARBINARY(n), BLOB Files, hashes, raw bytes
Boolean BOOLEAN TRUE / FALSE
Date and time DATE, TIME, TIMESTAMP, TIMESTAMP WITH TIME ZONE, INTERVAL Calendar dates, moments, durations
Other JSON, UUID, arrays, geometry Vary by database

Exact versus approximate numbers. Exact numeric types store a value precisely: the integer types hold whole numbers in a fixed range (SMALLINT is 2 bytes, about plus or minus 32 thousand; INTEGER is 4 bytes, about plus or minus 2.1 billion; BIGINT is 8 bytes, about 9.2 quintillion), and DECIMAL/NUMERIC hold fixed-point numbers with a declared precision and scale. Approximate types store a binary floating-point number: REAL (usually 4 bytes, about 7 significant digits) and DOUBLE PRECISION (8 bytes, about 15 to 16 digits). They cover a huge range and are fast, but results like 0.1 + 0.2 are only close to 0.3. Use exact types for money and counts, approximate types for measurements. In SQLite everything numeric is stored as an integer or an 8-byte REAL; the other types are labels only.

DECIMAL(10, 2) means up to 10 digits in total, 2 of them after the decimal point, so the largest value is 99999999.99. VARCHAR(100) means text of up to 100 characters. CHAR(2) is fixed-length text, padded with spaces in most databases; it's rarely worth using except for codes that are always the same length.

How SQLite stores values

SQLite doesn't store values by column type. Every individual value has one of five storage classes:

  • NULL: no value.
  • INTEGER: a whole number, stored in 1 to 8 bytes depending on its size.
  • REAL: an 8-byte floating-point number.
  • TEXT: a string, stored as UTF-8 here.
  • BLOB: bytes, stored exactly as given.

The type you write in CREATE TABLE only gives the column an affinity, a preference. SQLite tries to convert incoming values to that preference, and if it can't, it stores the value as is. The typeof() function shows what was actually stored:

SQL Affinity is only a preference

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

Look closely at the results:

  • '17' became the integer 17: the column prefers integers, and the text converted cleanly.
  • 'broken' was stored as text in an INTEGER column. No error.
  • 3.5 stayed a real, because converting it to an integer would lose information.
  • VARCHAR(5) didn't stop a 16-character name. SQLite ignores length limits.

This flexibility is convenient for quick scripts but dangerous for real data. If a typo puts text into a numeric column, sums and comparisons quietly go wrong later.

STRICT tables

Since version 3.37, SQLite lets you opt into real type checking by adding STRICT after the closing parenthesis. A strict table accepts only these column types: INTEGER (or INT), REAL, TEXT, BLOB and ANY. Values that can be converted losslessly still are ('17' still becomes 17), but anything else is rejected. This example fails on purpose:

SQL A STRICT table rejects bad values

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

The first two inserts succeed and the query shows both values stored as integers. Then the last insert stops the run with Error: cannot store TEXT value in INTEGER column readings.value. That's the behavior you get from PostgreSQL or SQL Server by default.

Use STRICT for new SQLite tables unless you have a reason not to. It costs nothing and catches whole classes of bugs. The one trade-off: type names like VARCHAR(50), DATE or DECIMAL aren't allowed in strict tables, so you write TEXT, REAL or INTEGER and add CHECK constraints for the rest (see Constraints).

Types across databases

When you move from SQLite to a server database, here's what to reach for. Each cell is the usual choice for that kind of data.

Data SQLite PostgreSQL MySQL / MariaDB SQL Server
Whole number INTEGER integer, bigint INT, BIGINT INT, BIGINT
Exact decimal INTEGER (cents) or TEXT numeric(12, 2) DECIMAL(12, 2) DECIMAL(12, 2)
Floating point REAL double precision DOUBLE FLOAT
Short text TEXT varchar(n) or text VARCHAR(n) NVARCHAR(n)
Long text TEXT text TEXT, LONGTEXT NVARCHAR(MAX)
True/false INTEGER 0/1 boolean BOOLEAN (= TINYINT(1)) BIT
Date TEXT 'YYYY-MM-DD' date DATE DATE
Date and time TEXT ISO 8601 or INTEGER Unix time timestamptz DATETIME, TIMESTAMP DATETIME2, DATETIMEOFFSET
Bytes BLOB bytea BLOB, VARBINARY VARBINARY(MAX)
JSON TEXT + JSON functions jsonb JSON NVARCHAR(MAX) (+ JSON in 2025)
UUID TEXT or BLOB uuid CHAR(36) / BINARY(16) UNIQUEIDENTIFIER

A few dialect details worth knowing:

  • In PostgreSQL, text and varchar perform the same; varchar(n) just adds a length check. Use timestamptz rather than plain timestamp for moments in time.
  • In MySQL, VARCHAR limits count characters, and you should use the utf8mb4 character set so emoji and all languages fit. TIMESTAMP only covers 1970โ€“2038; DATETIME covers years 1000โ€“9999.
  • In SQL Server, VARCHAR traditionally stored single-byte text, so international text uses NVARCHAR. BIT holds 0 or 1.

Here's the same table written for PostgreSQL. It's read-only, because it won't run in SQLite:

SQL
-- PostgreSQL
CREATE TABLE invoices (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_email varchar(254) NOT NULL,
  total numeric(12, 2) NOT NULL,
  paid boolean NOT NULL DEFAULT false,
  issued_on date NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

Storing money

Never store money in a floating-point column (REAL, FLOAT, DOUBLE). Floating-point numbers are binary fractions, and most decimal amounts, like 0.10, can't be represented exactly. The tiny errors add up:

SQL Why floats are wrong for money

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

0.1 + 0.2 comes out as 0.30000000000000004, so 0.1 + 0.2 = 0.3 returns 0, which means false. The leftover error is a tiny 5.551115123125783e-17. Errors like this pile up across millions of rows, and a ledger that is off by one cent fails its audit.

There are two safe approaches:

  1. Use an exact decimal type where one exists: NUMERIC(12, 2) in PostgreSQL, DECIMAL(12, 2) in MySQL and SQL Server. These store decimal digits exactly.
  2. Store whole minor units as integers: 1999 cents instead of 19.99 dollars. This works in every database, including SQLite, and integer arithmetic is exact.
SQL Money as integer cents

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

The total is exactly 9547 cents, shown as 95.47. You divide by 100.0 only at the very end, for display. Keep the currency next to the amount (currency TEXT NOT NULL) if you handle more than one, and remember that not every currency has two decimal places: the Japanese yen has none.

The sample databases on this site use REAL prices to keep queries simple. That's fine for learning; in production, use one of the two approaches above.

Storing dates and times

SQLite has no date type. It offers three conventions instead, and its date functions understand all three:

Format Example Good for
ISO 8601 text '2025-03-14', '2025-03-14 09:30:00' Readable, sorts correctly, the usual choice
Unix time (INTEGER) 1741944600 Compact timestamps, easy arithmetic
Julian day (REAL) 2460748.89583 Astronomy-style date math

ISO 8601 text works because its fields go from largest to smallest, so alphabetical order is chronological order. Other formats break sorting and comparisons:

SQL Date formats and sorting

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

Sorted by us_date, the December 2024 retro comes last, because '12/โ€ฆ' sorts after '02/โ€ฆ'. Switch to ORDER BY iso_date and the order is correct. Always store dates as YYYY-MM-DD and times as YYYY-MM-DD HH:MM:SS, and leave pretty formatting to the query or the application. The Date and Time Functions lesson shows how.

Two more rules that apply to every database:

  • Store moments in UTC. Convert to the user's time zone only for display. In PostgreSQL, timestamptz does this for you.
  • Use a date type for dates, not a timestamp. A birthday is a calendar date, not a moment. Storing it as midnight UTC makes it shift a day for users west of Greenwich.

Booleans

Standard SQL has BOOLEAN, and PostgreSQL and MySQL support it. SQLite has no separate boolean type: TRUE and FALSE are just aliases for 1 and 0. In SQLite, use an INTEGER column and a CHECK constraint so only 0 and 1 get in:

SQL Booleans in SQLite

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

TRUE is stored as the integer 1, and WHERE done works because any non-zero number counts as true.

Choosing types: a checklist

  • IDs and counts: integers. Use BIGINT in server databases for tables that may pass two billion rows.
  • Money: exact decimals or integer minor units, never floats.
  • Measurements (temperatures, distances, coordinates): floating point is fine.
  • Text: TEXT or VARCHAR(n). Add a length limit only when the domain truly has one, and make it generous: names and email addresses are longer than you think.
  • Codes that look like numbers (postal codes, phone numbers, account numbers): text. They can have leading zeros, and you never do arithmetic on them.
  • Dates and times: a real date type, or ISO 8601 text in SQLite. Store moments in UTC.
  • Yes/no flags: BOOLEAN, or 0/1 integers with a CHECK in SQLite.
  • In SQLite: prefer STRICT tables.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In a normal (non-STRICT) SQLite table with qty INTEGER, what does INSERT INTO t (qty) VALUES ('ten') do?

    Column types in ordinary SQLite tables are only affinities. Text that can't be converted to a number is stored as text.
  2. 2. Why should money not be stored in a REAL or FLOAT column?

    Values like 0.1 become tiny approximations, so sums drift: 0.1 + 0.2 = 0.3 is false. Use exact decimals or integer cents.
  3. 3. What does this return? SELECT '12/05/2024' < '02/10/2025';

    They are plain text, compared from the left: '1' is greater than '0', so the comparison is false (0). ISO 8601 dates avoid this problem.
  4. 4. Which type is best for a postal code like 02134?

    Postal codes can start with zero, can contain letters in many countries, and are never used in arithmetic, so they belong in a text column.
  5. 5. Which statement about SQLite STRICT tables is true?

    STRICT tables restrict the allowed type names. Text that converts losslessly (like '17') is still converted, and there's no VARCHAR(n) in strict tables at all.

Summary

  • A data type controls which values a column accepts and how they compare and sort.
  • Standard families: exact and approximate numbers, text, binary, boolean, date/time, plus JSON and UUID in many databases.
  • SQLite stores each value as NULL, INTEGER, REAL, TEXT or BLOB; column types are only affinities, and length limits are ignored.
  • Add STRICT to SQLite tables to get real type checking.
  • Store money as exact decimals or integer cents, never floats.
  • Store dates as real date types, or ISO 8601 text in SQLite; keep moments in UTC.
  • Codes that look like numbers belong in text columns.

Next: Auto-Increment and Identity Columns, where the database generates key values for you.