PostgreSQL Introduction

PostgreSQL (usually just "Postgres") is a free, open-source relational database. It is the default choice for new projects in much of the industry — it is strict about correctness, supports advanced features other databases charge for, and has no licensing cost.

Why Postgres

• Standards-compliant SQL, with genuinely good error messages.
• JSONB — store and index JSON documents alongside relational data.
• Full-text search built in, no separate search server needed.
• Window functions, CTEs, materialised views — all included.
• ACID transactions you can actually rely on.

Relational basics

Data lives in tables: rows (records) and columns (fields). Each table usually has a primary key that uniquely identifies a row, and tables reference each other through foreign keys.

Connecting with psql

psql -U postgres -d mydb           # connect
psql postgres://user:pass@host/db  # via URL

Useful meta-commands inside psql:

\l          list databases
\c dbname   connect to a database
\dt         list tables
\d books    describe the "books" table
\du         list users/roles
\q          quit

Creating a database and table

CREATE DATABASE bookstore;

CREATE TABLE books (
    id          SERIAL PRIMARY KEY,
    title       TEXT NOT NULL,
    author      TEXT,
    price       NUMERIC(10,2),
    published   DATE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

SQL conventions

• Keywords in UPPER CASE, identifiers in lower_snake_case.
• Statements end with a semicolon.
• Unquoted identifiers are folded to lowercase — Books and books are the same table. Avoid quoted "Books"; it forces you to quote it forever after.

Next: getting data out.