Tables, Rows, and Columns

~12 min · schema, basics

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

A spreadsheet with rules

A table is a spreadsheet that refuses to lie about itself. Every column has a name and a fixed data type; every row is one record. Unlike a spreadsheet, you cannot sneak a string into a number column or skip a required field — the database rejects the row at the door.

The vocabulary is worth getting comfortable with: a table holds rows (also called records, or tuples in the relational-algebra textbooks), each of which has values for the table's columns (attributes). A schema is the collection of tables, columns, types, and constraints that define what your database knows how to be.

Reading a CREATE TABLE like a sentence

Read the example below as: "There is a table called employees. Every row has an id (auto-generated, primary key), a first_name and last_name (required text), an email (required, must be unique), and a hired_on date that defaults to today." Once you can do that fluently, every other PostgreSQL idea becomes easier.

Code

A typical table·sql

CREATE TABLE employees (
    id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name  TEXT NOT NULL,
    email      TEXT UNIQUE NOT NULL,
    hired_on   DATE NOT NULL DEFAULT CURRENT_DATE,
    salary     NUMERIC(10,2)
);

INSERT INTO employees (first_name, last_name, email) VALUES
    ('Ada',   'Lovelace', 'ada@example.com'),
    ('Grace', 'Hopper',   'grace@example.com'),
    ('Alan',  'Turing',   'alan@example.com');

SELECT id, first_name, last_name, hired_on
FROM   employees
ORDER  BY hired_on, last_name;

Inspect with psql meta-commands·bash

\dt                  -- list tables
\d+ employees        -- detailed description: columns, types, indexes, constraints
\sf my_function      -- show function source

External links

PostgreSQL CREATE TABLE

Exercise

Design a CREATE TABLE for a 'todos' table that captures: id, title (required), body (optional), priority (only 'low'/'medium'/'high' allowed), due_date (optional), completed (defaults to false), created_at (defaults to now). Insert three rows and SELECT them ordered by priority then due_date.