A real database, and your first table

The database that is running right here

PostgreSQL is a relational database: it stores rows in tables, enforces the rules you declare, and answers questions written in SQL. It has been developed for more than thirty years and quietly runs an enormous share of the world's serious software — the phrase "just put it in Postgres" is a complete architecture in most companies.

The sandbox you are about to use is not a mock. It is PostgreSQL itself, compiled to WebAssembly, running inside this page — the same parser, the same planner, the same engine that a server would run. The only difference is where the server lives: in the tab instead of a machine somewhere.

One consequence worth knowing up front: the database is built fresh every time you press Run. Your script must create what it uses — which is also the habit a real project starts with, in files called migrations.

Tables, rows, columns

A table is a named shape: columns with types, and rows that follow it. CREATE TABLE books (id integer, title text, pages integer); declares the shape — three columns, two numbers and a piece of text. INSERT INTO books VALUES (1, 'Dune', 412); puts a row in it, and a SELECT that names the table asks for everything.

A relational database earns its name when tables point at each other — a review belongs to a book — and that is chapter seven. Everything before it is one table, read and written well; most of the skill of SQL lives there.

The shape of a script

Statements end with a semicolon, and the case of keywords is a habit rather than a rule — uppercase SELECT simply makes the parts of speech visible. A comment starts with -- and runs to the end of the line, which is where the explanation of a strange query belongs.

The example files are split the way a real project is: setup.sql builds the schema and the sample data, query.sql asks the question. Press Run and the question is answered against whatever the setup just built — change either file and the answer changes.

How the rows are kept

A table is stored as a heap: an unordered pile of pages, each holding rows, with free space tracked so an insert can find a home. Nothing in that structure says "row three comes after row two" — the order you see is whichever order the scan happened to read the pages in.

That is why a SELECT without ORDER BY may return the same rows in a different order tomorrow, especially after an update or a delete. If the order matters, it has to be asked for. A table is a set that happens to have a convenient reading order today.

Names fold to lower case

PostgreSQL folds unquoted identifiers to lower case, so CREATE TABLE Books, create table books and SELECT * FROM BOOKS all name the same table. The folding is what makes SQL forgiving about case, and keywords are case-insensitive for the same reason.

Double quotes stop the folding: "Books" is a different table from books, and from then on every reference needs the quotes exactly. That is a trap worth avoiding on day one — write names in lower case with underscores and never need the quotes.

What happens when you press Run

The server does not read the file a line at a time and act on it. It parses each statement, checks every table and column against the catalogue, plans how to fetch the rows, and only then executes. A syntax error stops at the first stage; a misspelled column is caught at the second, which is why the two messages read so differently.

The plan is the interesting part: the same SELECT can be answered by reading every row or by consulting an index, and the planner chooses from statistics it keeps about the data. Chapter twelve opens that decision up.

Try these next

• Add a fourth book and ask for title alone, then pages, title, and watch the column order follow the query.
• Write an INSERT that names its columns and leaves one out — the missing column becomes NULL rather than shifting the values.
• Create a table with a quoted mixed-case name and try to select from it without the quotes; read the error.

Create, insert, select

-- The shape first.
CREATE TABLE books (
    id integer,
    title text,
    pages integer
);

-- Then the rows.
INSERT INTO books VALUES
    (1, 'Dune', 412),
    (2, 'Solaris', 204),
    (3, 'Neuromancer', 271);

-- Ask for everything: * means every column.
SELECT * FROM books;

-- Ask for columns, in the order you name them.
SELECT title, pages FROM books;