PostgreSQL Tutorial with Runnable Examples
A free PostgreSQL tutorial with queries you run on the page: data types, CREATE TABLE, upserts, JSONB, dates, CTEs, window functions, indexes and VACUUM. Edit a query and run it again.
Start the coursePostgreSQL interview questions with runnable answersGetting Started
- PostgreSQL vs MySQLPostgreSQL and MySQL are both free relational databases. PostgreSQL has the richer SQL, JSONB and transactional DDL; MySQL is simpler to run where it is already the default.
- PostgreSQL vs SQL ServerPostgreSQL is free and open source; SQL Server is Microsoft's commercial database with T-SQL and a Windows-centered toolchain. Here is how their SQL, JSON, locking and licensing differ.
- PostgreSQL vs MongoDBPostgreSQL stores rows in tables with a fixed schema and can hold JSON documents in jsonb columns; MongoDB stores schema-flexible documents in collections and shards them across servers.
- List Databases and TablesIn psql, \l lists databases, \c switches to one and \dt lists its tables. In SQL, query pg_database, information_schema.tables and information_schema.columns.
Tables and Types
- Data TypesThe PostgreSQL data types you will use for almost every column: integers, numeric, text, boolean, timestamptz, uuid, jsonb, arrays and enums, with when to pick each.
- CREATE TABLECREATE TABLE defines a table's columns, types and constraints. Covers identity keys, NOT NULL, DEFAULT, UNIQUE, CHECK, IF NOT EXISTS, CREATE TABLE AS and LIKE.
- ALTER TABLEALTER TABLE changes an existing table: add, rename and drop columns, change a column's type with USING, set defaults and NOT NULL, and add constraints.
- Primary KeysHow to define a primary key in PostgreSQL, and how to generate it: identity columns, the older SERIAL, and UUIDs with gen_random_uuid(). Also composite keys and resetting a sequence.
- Foreign KeysA foreign key makes a column only accept values that exist in another table. Covers REFERENCES, ON DELETE CASCADE, SET NULL and RESTRICT, adding a key to an existing table, and indexing it.
- Enum TypesAn enum type is a fixed, ordered list of allowed values, created with CREATE TYPE ... AS ENUM. Covers adding and renaming values, listing them, sorting, and when a CHECK constraint is the better choice.
- Timestampstimestamptz stores a moment in time and shows it in the session's time zone; timestamp stores a wall-clock time with no zone. Neither stores the zone you wrote. Use timestamptz for almost everything.
Querying
- JoinsA join combines rows from two tables on a condition. INNER JOIN keeps matches, LEFT JOIN keeps every row of the first table, FULL JOIN keeps both, CROSS JOIN pairs everything, and LATERAL runs a subquery per row.
- DISTINCT ONSELECT DISTINCT ON (key) ... ORDER BY key, sort keeps the first row of each group, such as each customer's latest order. It is PostgreSQL-only.
- COALESCE, NULLIF and CASECOALESCE(a, b, ...) returns the first argument that is not NULL, NULLIF(a, b) turns a value into NULL, and CASE WHEN picks a value by condition.
- LIKE, ILIKE and RegexLIKE matches text against a pattern with % and _, ILIKE does the same ignoring case, and the ~ operators match POSIX regular expressions.
- CAST and ::CAST(value AS type) and value::type convert a value to another type in PostgreSQL. They are the same conversion; :: is PostgreSQL's shorter syntax.
Changing Data
- Upsert (ON CONFLICT)PostgreSQL upserts with INSERT ... ON CONFLICT: DO UPDATE with EXCLUDED, DO NOTHING, conditional updates, counters, and telling inserted rows from updated ones.
- RETURNINGRETURNING makes INSERT, UPDATE, DELETE and MERGE hand back the rows they changed: generated ids, new values, deleted rows, and which MERGE action ran.
- TransactionsHow PostgreSQL transactions work: BEGIN, COMMIT and ROLLBACK, savepoints, what an error does to an open transaction, and the isolation levels.
JSON and Arrays
- JSONBStore and query JSON in PostgreSQL with jsonb: the -> and ->> operators, @> containment, jsonb_set and other updates, turning arrays into rows, GIN indexes, and json vs jsonb.
- ArraysPostgreSQL array columns: declaring them, indexing from 1, checking whether an array contains a value with ANY, @> and &&, unnest, and the array functions you need.
- string_agg and array_aggstring_agg joins the values of a group into one string and array_agg collects them into an array. Ordering, DISTINCT, FILTER, NULL handling and the errors people hit.
Strings and Dates
- String FunctionsThe PostgreSQL string functions you use every day: substring, left and right, || and concat, split_part, position, replace, regexp_replace, trim, lpad and format.
- Date FunctionsWork with dates in PostgreSQL: now() and current_date, adding and subtracting intervals, date_trunc for grouping, extract and age, and formatting with to_char.
- generate_seriesgenerate_series returns a set of rows from a start to a stop value: numbers, dates and timestamps. Use it to fill gaps in reports, generate test data and build calendars.
Advanced Queries
- CTE (WITH)A common table expression names a subquery in a WITH clause so the main query can read it like a table. WITH RECURSIVE lets it refer to itself to walk trees and graphs.
- Window FunctionsWindow functions compute a value for each row from a group of related rows without collapsing them: rankings, running totals, moving averages and comparisons with the previous row.
Performance and Maintenance
- IndexesCREATE INDEX builds a B-tree index by default so PostgreSQL can find rows without reading the whole table. Other types (GIN, GiST, BRIN, hash) cover JSONB, arrays, full-text search, ranges and very large tables.
- EXPLAIN ANALYZEEXPLAIN shows the plan PostgreSQL picks for a query, and EXPLAIN ANALYZE runs the query and adds the real row counts and times of every step. Learn to read the plan tree and the common node names.
- Views and Materialized ViewsA view is a saved query that runs every time you read it. A materialized view stores the query's result and changes only when you run REFRESH MATERIALIZED VIEW.
- VACUUMVACUUM removes the dead row versions that UPDATE and DELETE leave behind so their space can be reused. Autovacuum runs it for you; this page shows when, how to tune it, and when VACUUM FULL is worth its lock.
Functions and Triggers
- Functions and ProceduresCREATE FUNCTION defines a function you call inside queries, written in SQL or PL/pgSQL. CREATE PROCEDURE defines a routine you run with CALL, which can commit transactions as it goes.
- TriggersA trigger runs a function automatically when rows are inserted, updated or deleted. In PostgreSQL you write a trigger function that returns trigger, then attach it to a table with CREATE TRIGGER.