Relational Model and SQL

Tables, rows, and typed columns

Before writing a single query it helps to know what the query is aimed at. PostgreSQL is a relational database management system, which means it is a system for managing data stored in relations, and relation is essentially a mathematical term for table. The two words name the same structure, so a sentence about relations is a sentence about tables.

The relational arrangement is a deliberate choice rather than the only possibility. Files and directories on a Unix-like operating system form a hierarchical database, and the object-oriented database is a more modern development. A relational system commits to one shape and builds everything else on top of it.

Each table is a named collection of rows, and each row of a given table has the same set of named columns. Each column is of a specific data type, so every stored value carries a declared kind instead of being loose text.

The shape of a row therefore belongs to the table, not to the row. A row cannot bring an extra column of its own, leave out one it does not need, or choose a different type for a column than its neighbours use.

For example, a products table declares id, name, and price. Every row in that table has exactly those three columns, price holds a numeric value in all of them, and no row is free to store a word there instead.

Two orderings are easy to confuse, and only one of them is promised. Columns have a fixed order in each row, but SQL does not guarantee the order of the rows within a table in any way, although rows can be explicitly sorted for display.

The practical consequence is worth remembering: running the same unsorted query twice can present the same rows in a different sequence, and nothing is broken when it does. Presentation order has to be requested.

Tables themselves are grouped into databases, and a collection of databases managed by a single PostgreSQL server instance constitutes a database cluster.

Identifying rows and relating tables

Rows are unordered and interchangeable in shape, so the model needs a way to say this row. That is the job of constraints.

A unique constraint ensures that the data contained in a column, or a group of columns, is unique among all the rows in the table. A primary key constraint indicates that a column, or group of columns, can be used as a unique identifier for rows in the table, and it requires that the values be both unique and not null.

A table can carry any number of unique constraints, so more than one column may qualify as an identifier, but a table can have at most one primary key, and that single choice is the one the system treats as the identity of a row. Adding a primary key also forces the listed columns to be marked not null.

References between tables are built on that identity. A foreign key constraint specifies that the values in a column, or a group of columns, must match the values appearing in some row of another table, which is how referential integrity between two related tables is maintained.

The two sides have names worth keeping straight. The table that carries the constraint is the referencing table, and the table it points at is the referenced table.

When no column list follows the referenced table name, the primary key of the referenced table is used as the referenced column. This is why the primary key defines the default target for foreign keys aimed at its table: write the reference without a column list and it resolves to that one declared identity.

Not every column can be pointed at. A foreign key must reference columns that either are a primary key or form a unique constraint, or are columns from a non-partial unique index, so the referenced columns always have an index that resolves a reference to at most one row.

The rule is about resolution, not speed. A reference is only meaningful when it lands on at most one row.

Put the two rules together on a real design. Suppose a staff table has two columns that are each unique and never null: employee_no and email. Other tables must be able to reference staff without naming a column list, and tooling must be able to identify a row from one declared identity.

The workable design declares one of the two columns the primary key and leaves the other as a unique constraint. The primary key becomes the default target that a bare reference resolves to; the other column stays a perfectly good identifier and can still be referenced explicitly by naming it.

Two tempting alternatives fail:

• Declaring both as primary keys is impossible, because a table can have at most one primary key.
• Declaring both as plain unique constraints leaves no primary key, so a reference written without a column list has no default target to resolve to.

Statements, clauses, expressions, and result sets

A statement is one complete command handed to the server. The process of retrieving data, or the command that retrieves it, is called a query, and in SQL the SELECT command is used to specify queries.

A statement is assembled from clauses, the labelled parts that each answer one question about the work.

Splitting the work this way is what makes a query readable: each decision has one home, and changing the order of presentation never changes which values are produced.

Two of those clauses stretch further than they first appear.

FROM followed by one table name is a simple kind of table expression, while in general a table expression can be a complex construct of base tables, joins, and subqueries. So the same clause covers reading one table and combining several.

A select list can select a subset of the available columns or make calculations using the columns, so one entry can add two numeric columns together instead of naming a single column. The calculation becomes an output column, and the set of rows returned is untouched.

That division of labour decides where a computed value belongs: putting it in the table expression would risk changing the rows, and a sort specification only arranges them.

Expressions are the smaller pieces inside those clauses that compute a single value, and they do not need a table underneath them. The table expression can be omitted entirely, which lets SELECT work as a calculator over constants and function results.

A select list written as an asterisk means all columns that the table expression happens to provide, so such a query returns every row and every user-defined column of the named table.

What the statement hands back is the result set. How that result is presented depends on the client application: an interactive terminal draws a text table on the screen, while client libraries offer functions that extract individual values from the query result.

Key takeaways

• A table is a named collection of rows, every row carries the same named columns, and each column is of a specific data type.
• Column order inside a row is fixed, while the order of the rows in a table is not guaranteed and has to be requested with a sort.
• A primary key is unique and not null, a table can have at most one of them, and a reference without a column list resolves to it.
• A foreign key may reference only a primary key, a unique constraint, or a non-partial unique index.
• Clauses divide a statement into labelled parts — select list, table expression, sort specification — and expressions compute the individual values inside them.
• The result set is what the statement returns, and a query with no table expression simply evaluates its expressions.