PostgreSQL Fundamentals

why postgresql specifically

PostgreSQL is the default choice for new projects as of 2026 — it's an open-source, standards-compliant relational database with a genuinely unusual property for its category: it keeps gaining serious features (JSONB, full-text search, window functions, extensions like pgvector) without becoming less reliable. Everything in this section after this page is PostgreSQL-specific; the SQL Fundamentals page covers what's portable to other engines.

installing and connecting

# Ubuntu/Debian
sudo apt install postgresql

# macOS (Homebrew)
brew install postgresql@16

# Connect to the default database as the postgres superuser
sudo -u postgres psql

# Or, once you have your own role/database:
psql -U myuser -d mydatabase -h localhost

psql is the official command-line client — it's what you'll use constantly, including for things a GUI makes awkward, like piping a script in from a file or a Unix pipeline.

essential psql meta-commands

Command	Does
`\l`	list databases
`\c dbname`	connect to a different database
`\dt`	list tables in the current schema
`\d tablename`	describe a table — columns, types, indexes, constraints
`\du`	list roles/users
`\x`	toggle expanded output — invaluable for wide rows
`\timing`	show how long each query took
`\q`	quit

These start with a backslash and are psql-specific, not SQL — they never work inside application code, only interactively (or in a .sql script run through psql itself).

your first table

CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  done BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO tasks (title) VALUES ('Write PostgreSQL fundamentals page');

SELECT * FROM tasks;
--  id |               title                | done |          created_at
-- ----+-------------------------------------+------+-------------------------------
--   1 | Write PostgreSQL fundamentals page  | f    | 2026-08-09 10:14:02.881+00

SERIAL is shorthand for an auto-incrementing integer backed by a sequence — the modern equivalent, GENERATED ALWAYS AS IDENTITY, is preferred in new schemas but you'll see SERIAL constantly in existing code.

databases, schemas, and search_path

PostgreSQL has a three-level namespace most engines don't: a server hosts multiple databases, each database contains multiple schemas (the default is public), and each schema contains tables. Unqualified table names resolve against search_path, which defaults to public. This matters the first time you see schema.table notation and wonder what the prefix is.

where to go from here

Data Types & Schema Design — constraints and Postgres-specific types beyond what's shown here.
Users, Roles & Permissions — setting up a real role instead of using the postgres superuser.
SQL from Python — connecting from application code instead of psql.