Core Data Types Across Dialects

SQL data types are the promises a column makes: what kind of value can live here, how the database stores it, and which operations make sense. This lesson gives you the core map — numbers, text, booleans, and time values — so you can read schemas without guessing and choose types without memorizing every dialect's catalog.

Types are promises, not decorations

The menu table already carries several everyday types. Ask Postgres what kind of value each column is holding:

SELECT pg_typeof(id) AS id_type,
       pg_typeof(name) AS name_type,
       pg_typeof(price) AS price_type,
       pg_typeof(in_stock) AS stock_type
FROM menu_items
WHERE id = 1;

 id_type | name_type | price_type | stock_type
---------+-----------+------------+------------
 integer | text      | numeric    | boolean
(1 row)

Those are not labels for humans only. They decide which comparisons are legal, how arithmetic behaves, how much range a value can have, and what "missing" means when a value is not known. A price can be multiplied. A name can be measured or glued to other text. A boolean such as in_stock is already a truth value, not the words 'yes' or 'no'.

When you ask for an operation that does not make sense, the engine says so:

SELECT price + name FROM menu_items WHERE id = 1;

ERROR:  operator does not exist: numeric + text
LINE 1: SELECT price + name FROM menu_items WHERE id = 1
                     ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

That hint points toward a future tool — explicit casts — but the lesson here is simpler: a type is a boundary. SQL will not pretend a product name is a number just because you used +.

Numbers: exact, approximate, and large enough

Most schema choices start with the question "what kind of number is this?"

| Type family | Use it for | | --- | --- | | INTEGER | Whole numbers in the ordinary app range: counts, positions, small ids. | | BIGINT | Whole numbers that may grow past integer range: large ids, event counters, long-running ledgers. | | NUMERIC / DECIMAL | Exact decimal values: prices, rates, measurements where 3.20 should stay exactly 3.20. | | REAL / DOUBLE PRECISION | Approximate floating-point values: scientific measurements, coordinates, scores where tiny rounding noise is acceptable. |

The menu uses numeric(5,2) for prices: up to five total digits, two after the decimal point. You can see exact decimal arithmetic in the grid:

SELECT name, price, price * 12 AS dozen_price
FROM menu_items
WHERE category = 'pastry'
ORDER BY price;

     name      | price | dozen_price
---------------+-------+-------------
 Croissant     |  3.20 |       38.40
 Banana Bread  |  3.60 |       43.20
 Cinnamon Roll |  4.10 |       49.20
(3 rows)

The important choice is not "smallest type possible." It is the smallest honest container. If an id can reasonably outgrow INTEGER, choose BIGINT before the table becomes famous. If a value is money-like, choose an exact decimal family before rounding becomes a report bug. If the value is a sensor reading, an approximate type may be the honest one.

Text: TEXT and VARCHAR(n)

Text columns hold strings, but the common beginner mistake is believing VARCHAR(50) is a performance spell. In Postgres, TEXT is an unlimited variable-length text type, and VARCHAR(n) is variable-length text with an enforced maximum length. That n is mainly validation: "this code must fit in 12 characters," not magic storage compression.

The menu names are all text, but they do not need to be the same length:

SELECT name, length(name) AS characters
FROM menu_items
WHERE category IN ('tea', 'drinks')
ORDER BY characters, name;

      name       | characters
-----------------+------------
 Sencha          |          6
 Earl Grey       |          9
 Sparkling Water |         15
(3 rows)

Use TEXT when the natural rule is "some text." Use VARCHAR(n) when the limit is a real business rule you want the database to enforce: a two-letter country code, a short display slug, a fixed external identifier. Do not pick VARCHAR(255) just because old examples did.

Booleans, dates, times, and moments

Booleans are for true/false facts: in_stock, is_active, email_verified. The grids in this course render booleans the way Postgres does at the terminal: t for true, f for false. That compact output is still a real boolean, not text.

Dates and times answer different questions:

| Type | Meaning | | --- | --- | | DATE | A calendar day, with no clock time. | | TIME | A time of day, with no date. | | TIMESTAMP | A date plus clock time, without a time-zone-aware instant. | | TIMESTAMPTZ | A date plus clock time interpreted as an exact moment in time. |

The daily sales table uses a plain DATE because "sales day" is a calendar bucket:

SELECT day, pg_typeof(day) AS day_type, revenue
FROM daily_sales
WHERE branch = 'center'
ORDER BY day
LIMIT 3;

    day     | day_type | revenue
------------+----------+---------
 2026-03-02 | date     |  830.00
 2026-03-03 | date     |  905.50
 2026-03-04 | date     |  760.00
(3 rows)

And Postgres has separate type names for clock-time values and timestamp values:

SELECT pg_typeof(TIME '08:30:00') AS time_type,
       pg_typeof(TIMESTAMP '2026-03-02 08:30:00') AS timestamp_type,
       pg_typeof(TIMESTAMPTZ '2026-03-02 08:30:00+00') AS timestamptz_type;

       time_type        |       timestamp_type        |     timestamptz_type
------------------------+-----------------------------+--------------------------
 time without time zone | timestamp without time zone | timestamp with time zone
(1 row)

The time-zone details get their own lesson later. For now, store the value that matches the fact: a birthday is a DATE, a shop opening hour is a TIME, and a payment event that happened at one exact instant usually wants TIMESTAMPTZ.

Dialects have accents

SQL engines agree on the big categories and disagree on enforcement details. Postgres is strict about the types you declare: a boolean column is a boolean column, a numeric column is numeric, and the error above is the engine refusing to blur the line.

SQLite is different. Ordinary SQLite tables use type affinity: the declared type guides how values are treated, but the engine is historically flexible about what can be stored. Modern SQLite also has STRICT tables when you want tighter enforcement. MySQL has its own classic gotcha: BOOL and BOOLEAN are synonyms for TINYINT(1), with zero treated as false and nonzero values treated as true.

None of that makes one engine fake. It means the language has accents, and type-checking is one of the places you hear them. When you move SQL between engines, check the type rules before assuming identical behavior.

Types answer "what kind of value is this?" The next lesson handles the other question every real dataset asks: what happens when the value is missing or unknown?