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 "$user", public — a schema named after the current user is checked first, if one exists. 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.