Chapter 01 · basic · 5 min
What a table really is
You've been writing SELECT against tables for a while now. This chapter is about the other side: what a table actually is before any query touches it, and why the type on each column isn't just documentation.
We'll use a small warung's products table.
The dataset
One table, products (id, name, price, in_stock, description). Two rows have no description at all — useful for seeing how NULL behaves differently from an empty string.
Schema
| id | INT |
| name | TEXT |
| price | NUMERIC(10,2) |
| in_stock | BOOLEAN |
| description | TEXT |
Example data
A table is a fixed set of typed columns
CREATE TABLE declares every column's name and type up front: id INT, price NUMERIC(10,2), in_stock BOOLEAN. Every row that ever goes into this table has to fit that shape — the same five columns, the same five types, no exceptions.
Before you run it — how many columns come back, and which one do you expect to be TRUE/FALSE rather than text or a number?
The type is enforced, not decorative
price is declared NUMERIC(10,2) — a number. Try to insert text into it and Postgres doesn't quietly convert or truncate it: it refuses the entire row.
Before you run it — does this insert succeed with `price` stored as text, or does something stop it?
NULL means "unknown", not "empty"
Two rows above have no description. That's stored as NULL — not an empty string '', and it behaves differently. A NULL compared with = never returns true, not even against another NULL, because SQL treats "unknown compared to unknown" as itself unknown, not equal.
Before you run it — two rows have a NULL description. How many rows do you think this returns?
IS NULL is the actual check
To ask "is this unknown", SQL has a dedicated operator: IS NULL (and IS NOT NULL). This is the correct version of the query above.
Before you run it — same two rows as before. Does this version find them?
Casting between types with ::
Sometimes you want a value as a different type on purpose — a number formatted as text, or text parsed as a number. Postgres's cast operator is ::type.
Before you run it — `price_label` is built from `price::text || ' MYR'`. What would happen if you dropped the `::text` and tried to `||` a NUMERIC directly?
If you've only used spreadsheets
A spreadsheet column has no declared type — you can put a number in row 3 and a sentence in row 4 of the same column, and it just displays both. A SQL table can't: every row is checked against the column's type the moment it's inserted, which is exactly why products.price can never silently become the text "five fifty". That upfront rigidity is what makes a million-row table trustworthy — a formula three rows down can't be quietly reading text where it expects a number.
Next: a table's type keeps price numeric, but nothing so far stops two rows from both claiming id = 1. That's what a primary key is for.