Data Types (and the Strange Case of NULL)

The last page said each column accepts only a certain kind of value; this page names the kinds. A column's data type declares what may live in it — text, a whole number, a decimal, a date, a true/false — and the database refuses anything that doesn't fit. Remember the phantom seat "Gs7" from Chapter 1, the typo that became a stored fact? Types are the first wall that stops that whole family of errors, at the moment of typing, before bad data exists.

And then there is the odd one out. Alongside all the ordinary values a column can hold, there is a special marker called NULL — the database's way of recording "no value here." It is not a type; it is an absence that any column can carry, and it behaves unlike anything else in the building. Professionals a decade into their careers still trip over it, so this page gives it the slow, honest treatment it deserves.

The Everyday Types

Five types cover most of real life. TEXT holds words and characters: a title, a name, an email. INTEGER holds whole numbers: a runtime in minutes, a screen number. For money there are decimal types like NUMERIC — exact fractional numbers, so a ticket price of 11.50 stays exactly 11.50. DATE and TIMESTAMP hold calendar dates and date-plus-time moments, like when a screening starts. And BOOLEAN holds true or false — is this screening sold out, yes or no.

Here is the Marquee's second table, screenings — the showtimes board — declared with a type on every column:

CREATE TABLE screenings (
  screening_id  INTEGER,
  film_id       INTEGER,
  screen        INTEGER,
  starts_at     TIMESTAMP,
  price         NUMERIC(6,2)
);

Read the new pieces: starts_at is a TIMESTAMP, a full date-and-time like Friday the 13th at 20:00; price is NUMERIC(6,2), a decimal with up to six digits, two of them after the point — room for prices up to 9999.99, which should cover the Marquee for a while. Each type is a small declaration of what the column protects: no letters in screen, ever; no "sometime in March" in starts_at.

An analogy, briefly: labeled storage bins. The bin marked "screws" rejects a sandwich — not because the bin is fussy, but because everyone who reaches into it afterwards is counting on finding screws. NULL, which we'll meet below, is a bin wearing a "contents unknown" tag — which is a different thing entirely from an empty bin. Analogy mapped; back to the real terms.

Types Are Promises

The refusal is the visible half of what types do. The valuable half is what the refusal buys: because price is numeric, you can safely add prices, average them, compare them — every value in that column is guaranteed to be a number, with no exceptions to check for. A spreadsheet with "12 euros" typed into one price cell can never make that promise, and every sum over that column is quietly wrong.

Watch the promise being enforced. Suppose a tired hand tries to record a screening with the price written out as words:

INSERT INTO screenings (screening_id, film_id, screen, starts_at, price)
VALUES (12, 3, 1, '2026-07-17 20:00', 'twelve euros');

The statement tries to store the text "twelve euros" in the price column, and the database hands it straight back with an error: that column holds NUMERIC, and "twelve euros" is not a number. Nothing is stored, nothing half-happens — the write simply never enters the building. Compare that with the spreadsheet, which accepted "Gs7" without a blink, and you can feel the difference between storage and protection.

Dates deserve one extra sentence of respect, because they are where typing-as-text hurts most. Stored as text, "2026-3-9" and "March 9" and "09/03/26" are three unrelated strings — they can't be compared, sorted, or asked "which screenings are after seven tonight?" Stored in a date type, they are the same moment, and every one of those questions works.

NULL Is "Unknown" — Not Zero, Not Empty

Now the strange case. Some of Lora's customers give an email address; some don't. What goes in the email column for a customer who didn't? Not an empty text '' — that would claim "we asked, and their email is the empty string," which is a known (and nonsensical) value. Not some made-up placeholder. The honest answer is NULL: we don't know. NULL is the database recording an absence — a question with no answer on file.

The distinction sounds philosophical and is intensely practical. A film with runtime_min = 0 claims a zero-minute film — a known fact, and a false one. A film with runtime_min NULL claims nothing at all: runtime not on file. Zero, empty string, and NULL are three different statements, and reports that confuse them produce wrong numbers with great confidence.

Because NULL means unknown, it infects comparisons in a way nothing else does: asking whether NULL = NULL does not give true — it gives unknown, because comparing two unknowns can't honestly promise they match. This is the one behavior on this page worth memorizing now, and it is why SQL has a special question — IS NULL — for finding the gaps. You'll meet it properly in Chapter 3; this paragraph is just planting the flag where the trap is.

One Honest Note on Engines

Types vary slightly by engine. PostgreSQL is strict and rich — it rejects misfit values firmly and offers many specialized types. SQLite is famously relaxed: by default it will often store a misfit value rather than refuse it, trading protection for convenience. The concepts on this page hold everywhere; the exact type names and the strictness of enforcement are the first place you'll notice engines having personalities. One sentence of honesty, and onward.