Tables, rows and columns

ConceptFoundation4 min read10 min hands-on#fundamentals#sql#data-modeling

The idea in 30 seconds

A relational database stores everything in tables. Every row of a table has the same named, typed columns, but the rows themselves have no order and no built-in identity. Get used to those two facts early: most surprises in SQL come from forgetting them.

Helps to know first: What data is, and why we need a database

Everything is a table

A relational database stores all its data in tables. “Relation” is the mathematical word for a table, which is where the name comes from.

A table looks like a spreadsheet, with stricter rules:

Columns are fixed. Each has a name and a data type: name is text, joined_on is a date. Every row has exactly the same columns.
Rows are the records. A table can hold none or billions.

Here is customers from the practice database used throughout this section:

id	name	email	city	joined_on
1	Asha Rao	asha@example.com	Pune	2025-01-12
2	Ben Okafor	ben@example.com	Lagos	2025-02-03
5	Elena Petrova	elena@example.com		2025-04-15
…

Elena’s city is empty. That is not an empty string; it is NULL, meaning “unknown”. NULL behaves in surprising ways, and SQL basics: reading data covers it properly.

Tables live in a database, and one PostgreSQL server can hold many databases. The next topic sets one up.

Get the practice database

Every topic in this section uses the same small shop: customers, products, orders and the items in each order. Download shop.sql, and once PostgreSQL is installed (next topic), load it with:

createdb shop
psql -d shop -f shop.sql

The file recreates its tables every time you run it, so you can always get back to a clean copy.

Rows have no order

This is the first rule that catches people out: a table has no order. PostgreSQL may return rows in any order it likes unless you ask for one with ORDER BY.

PredictSame query, different order

Run SELECT id, name, city FROM customers; on a freshly loaded shop. The rows come back with ids 1 to 8, in order. Then run UPDATE customers SET city = 'Mumbai' WHERE id = 1; and the same SELECT again. In what order do the rows come back now?

Asha, id 1, moves to the end. We ran it on PostgreSQL 18:

 id |     name      |   city
----+---------------+----------
  2 | Ben Okafor    | Lagos
  3 | Chen Wei      | Shanghai
  4 | Divya Iyer    | Pune
  5 | Elena Petrova |
  6 | Farhan Ali    | Karachi
  7 | Grace Kim     | Seoul
  8 | Hugo Martin   |
  1 | Asha Rao      | Mumbai

Without ORDER BY, PostgreSQL returns rows in whatever order is cheapest, which here is roughly the order they sit on disk. The update wrote a new version of Asha’s row in a new place, so it now comes last. Code that relies on “the order rows usually come back in” works until the day it doesn’t.

The rule: if the order matters, say ORDER BY. Every time.

Rows have no identity of their own

The second rule: SQL does not give rows a hidden id. If two rows have identical values in every column, nothing distinguishes them, and you cannot update or delete just one of them.

That is why well-designed tables have a column, or a group of columns, that is different for every row: a key. In customers it is id. The next page is about keys and how they connect tables.

Check yourself

What guarantees does a column give you that a spreadsheet column does not?
Show answer
Every row has the column, and every value in it has the column’s data type. You cannot put a word in a date column.
I could explain this without looking
A report lists customers in the order SELECT * FROM customers returns them. Why is that a bug, even if it looks right today?
Show answer
SQL does not guarantee row order. PostgreSQL returns rows in whatever order is cheapest, which can change after updates, deletes or as the table grows. Only ORDER BY guarantees an order.
I could explain this without looking
Why does a table need a key if rows already look different from each other?
Show answer
Nothing stops two rows from being identical, and SQL gives rows no built-in identity. A key is a column (or set of columns) guaranteed to differ for every row, so each row can be found, referenced and changed on its own.
I could explain this without looking

Sources

Where each claim on this page comes from.

PostgreSQL documentation: 2.2 Concepts

Primary· §2.2 Concepts

Relation is the mathematical term for table; each row has the same named columns of a specific type; SQL does not guarantee row order; tables are grouped into databases, and databases on one server form a cluster.

PostgreSQL documentation: 5.1 Table Basics

Primary· §5.1 Table Basics

Columns have a fixed number, order and names; rows are returned in unspecified order unless sorted; SQL does not give rows unique identifiers, so identical rows are possible.

PostgreSQL documentation: 66.7 Heap-Only Tuples (HOT)

Primary· §66.7 Heap-Only Tuples (HOT)

Under MVCC, an update adds a new version of the row to the table.