Tables, Rows and Columns
A PostgreSQL error message says "new row for relation "rentals" violates check constraint". Relation? Nobody created a relation. The database and its documentation speak two vocabularies at once: the everyday one and the mathematical one it was built on. A DBA needs both.
The rule
A table is a relation; a row is a tuple; a column is an attribute.
The relational model, proposed by E. F. Codd in 1970, describes data with three terms that map straight onto what you see:
| Relational model | SQL and PostgreSQL | Pedal example |
|---|---|---|
| Relation | Table | bikes |
| Tuple | Row | the bike with id 7 |
| Attribute | Column | model |
Each column has a name and a type. Each row supplies one value for every column. The table's definition (its columns and rules) is its heading; the set of rows at any moment is its body.
CREATE TABLE bikes (
bike_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
model text NOT NULL,
city text NOT NULL,
available boolean NOT NULL DEFAULT true
);Two ideas from the model matter every day:
- Rows have no guaranteed order. Without
ORDER BY, PostgreSQL may return rows in any order, and the order can change after an update or a vacuum. - Rows should be distinguishable. A relation is a set, so every row should be unique. A primary key enforces that.
Internally PostgreSQL still says "relation" for anything shaped like a table, which is why tables, indexes, views and sequences all appear in the catalogue pg_class. That is why the word turns up in error messages.
The shortcut
Translate any sentence into the three layers: the kind of thing → the table; one instance → a row; a property → a column. If a property can hold a list, it wants its own table.
The kind of thing
"Stations" are a new kind of thing, so a new table
stations.
The trap is trusting row order. A report that "always came out sorted" without ORDER BY will break one day, and not in testing.
Drills
Answer fast — trust the rule.
In relational-model terms, what is one row of bikes?
SELECT * FROM bikes; returned rows sorted by id yesterday. What is guaranteed today?
Why does PostgreSQL talk about a "relation" when an index fails to build?
A station can hold many bikes. Where does the link belong?
Remember
- A table is a relation; a row is a tuple; a column is an attribute.
- Rows have no order without ORDER BY, and a primary key keeps them unique.
- A property holding a list wants its own table or a key on the many side.