PostgreSQL — Complete Tutorial
6 modules · 12 examples · Click ▶ Run Query to see results
💡 How to use this tutorial
Read each explanation, study the SQL, then click ▶ Run Query to see the result set.
What is PostgreSQL?
PostgreSQL (often called Postgres) is the world's most advanced open-source relational database, first released in 1996. It is ACID-compliant, fully SQL-standard-compliant, and trusted by companies like Apple, Instagram, Spotify, and Reddit. Unlike basic SQL databases, Postgres adds powerful features: JSON support, full-text search, window functions, CTEs, custom types, extensions, and much more. It runs on every major operating system and is 100% free.
Connecting and First Queries
PostgreSQL uses psql as its command-line client. After connecting, you can run SQL immediately. PostgreSQL is case-insensitive for keywords but case-sensitive for quoted identifiers. Comments start with -- for single-line or /* */ for multi-line. Every statement ends with a semicolon.
-- Connect via psql (in terminal)
-- psql -U postgres -d mydb -h localhost
-- Check PostgreSQL version
SELECT version();
-- List all databases
\l
-- Connect to a database
\c mydb
-- List all tables in current schema
\dt
-- Describe a table structure
\d employees
-- Basic query
SELECT current_date, current_time, current_user;
-- PostgreSQL-specific functions
SELECT
now() AS current_timestamp,
pg_database_size('mydb') AS db_size_bytes,
inet_server_addr() AS server_ip;
-- Show all schemas
SELECT schema_name
FROM information_schema.schemata;psql has many backslash commands (\l, \dt, \d table, \dn for schemas). Use \? to see all of them. The \timing command toggles execution time display — great for performance tuning. \e opens the last query in your $EDITOR for multi-line editing.
PostgreSQL Data Types
PostgreSQL has a rich type system far beyond basic SQL: integers, serials (auto-increment), floating points, text, varchar, boolean, dates, timestamps with timezone, JSON, JSONB, arrays, UUIDs, network addresses, geometric types, and custom enum types. Choosing the right type improves storage efficiency and query performance.
-- PostgreSQL data type overview
-- Integers
SELECT
42::integer,
9223372036854775807::bigint,
32767::smallint;
-- Auto-increment (SERIAL / IDENTITY)
CREATE TABLE demo_serial (
id SERIAL PRIMARY KEY, -- auto-increment integer
name TEXT NOT NULL
);
-- Floating point and exact decimal
SELECT
3.14::real, -- 4-byte float
3.14159265358979::float8, -- 8-byte float (float8 = double)
123.456::numeric(10, 3); -- exact decimal — use for money!
-- Text types
SELECT
'Hello'::text, -- unlimited length
'Hi'::varchar(5), -- variable, max length
'X'::char(1); -- fixed length, padded
-- Boolean
SELECT true, false, 'yes'::boolean, 'no'::boolean;
-- Date and time
SELECT
'2026-04-28'::date,
'14:30:00'::time,
'2026-04-28 14:30:00'::timestamp,
'2026-04-28 14:30:00+02'::timestamptz; -- WITH timezone
-- UUID
SELECT gen_random_uuid(); -- requires pgcrypto or pg 13+
-- JSON vs JSONB
SELECT
'{"name":"Alice","age":25}'::json,
'{"name":"Alice","age":25}'::jsonb; -- indexed, fasterAlways use timestamptz (timestamp with time zone) instead of timestamp when storing dates that matter across time zones. Postgres stores timestamptz in UTC internally and converts to the session time zone on output. Use NUMERIC (not FLOAT) for money — floats have rounding errors that accumulate.
Creating Tables (DDL)
DDL (Data Definition Language) covers creating, modifying and dropping database objects. PostgreSQL's CREATE TABLE supports constraints, defaults, check conditions, foreign keys, and generated columns. ALTER TABLE lets you add or remove columns and constraints after creation. Using proper constraints enforces data integrity at the database level — not just in application code.
CREATE TABLE with Constraints
Constraints enforce data rules at the database level. NOT NULL prevents empty values. UNIQUE ensures no duplicates in a column. CHECK validates a condition. PRIMARY KEY uniquely identifies each row. FOREIGN KEY links rows to another table. DEFAULT sets a fallback value. Generated columns compute values automatically.
-- Create a schema to organise tables
CREATE SCHEMA IF NOT EXISTS shop;
-- Customers table
CREATE TABLE shop.customers (
id BIGSERIAL PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
phone VARCHAR(20),
date_of_birth DATE,
is_active BOOLEAN NOT NULL DEFAULT true,
credit_limit NUMERIC(10,2) NOT NULL DEFAULT 0.00
CHECK (credit_limit >= 0),
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Products table
CREATE TABLE shop.products (
id BIGSERIAL PRIMARY KEY,
sku TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
description TEXT,
price NUMERIC(10,2) NOT NULL CHECK (price > 0),
stock_qty INTEGER NOT NULL DEFAULT 0 CHECK (stock_qty >= 0),
category TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Orders table with foreign keys
CREATE TABLE shop.orders (
id BIGSERIAL PRIMARY KEY,
customer_id BIGINT NOT NULL
REFERENCES shop.customers(id)
ON DELETE RESTRICT,
status TEXT NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending','paid','shipped','delivered','cancelled')),
total NUMERIC(10,2) NOT NULL DEFAULT 0,
notes TEXT,
ordered_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Add indexes for common lookups
CREATE INDEX idx_orders_customer ON shop.orders(customer_id);
CREATE INDEX idx_orders_status ON shop.orders(status);
CREATE INDEX idx_products_category ON shop.products(category);ON DELETE RESTRICT (the default) prevents deleting a customer who has orders. Alternatives: ON DELETE CASCADE (delete orders when customer deleted), ON DELETE SET NULL (set customer_id to NULL), ON DELETE SET DEFAULT. Choose based on your business rules — RESTRICT is the safest default.
ALTER TABLE — Modifying Structure
ALTER TABLE lets you change table structure after creation without losing data. You can add or drop columns, change types, add or remove constraints, rename columns and tables, and set or drop defaults. In production, some ALTER operations lock the table — use CONCURRENTLY where available to avoid downtime.
-- Add a new column
ALTER TABLE shop.customers
ADD COLUMN loyalty_points INTEGER NOT NULL DEFAULT 0;
-- Add column with a constraint
ALTER TABLE shop.customers
ADD COLUMN tier TEXT NOT NULL DEFAULT 'bronze'
CHECK (tier IN ('bronze', 'silver', 'gold', 'platinum'));
-- Rename a column
ALTER TABLE shop.customers
RENAME COLUMN phone TO phone_number;
-- Change a column type (safe if compatible)
ALTER TABLE shop.customers
ALTER COLUMN loyalty_points TYPE BIGINT;
-- Set a new default
ALTER TABLE shop.customers
ALTER COLUMN credit_limit SET DEFAULT 500.00;
-- Drop a default
ALTER TABLE shop.products
ALTER COLUMN stock_qty DROP DEFAULT;
-- Add a unique constraint
ALTER TABLE shop.products
ADD CONSTRAINT uq_product_name UNIQUE (name);
-- Drop a constraint
ALTER TABLE shop.products
DROP CONSTRAINT uq_product_name;
-- Add a foreign key after creation
ALTER TABLE shop.orders
ADD COLUMN product_id BIGINT
REFERENCES shop.products(id) ON DELETE SET NULL;
-- Rename the table
ALTER TABLE shop.products RENAME TO shop.items;
ALTER TABLE shop.items RENAME TO shop.products; -- rename back
-- Drop a column
ALTER TABLE shop.customers
DROP COLUMN date_of_birth;
SELECT 'All alterations completed' AS result;Adding a column with a DEFAULT in PostgreSQL 11+ is instant — it stores the default in the catalog and does not rewrite the table. In PostgreSQL 10 and earlier, adding a NOT NULL column with a default rewrites the entire table, which can take minutes on large tables. Always test migrations on a copy of production data first.
Inserting & Modifying Data
DML (Data Manipulation Language) covers inserting, updating, and deleting rows. PostgreSQL extends standard SQL with powerful DML features: INSERT ... ON CONFLICT (upsert), RETURNING (get inserted/updated values back), UPDATE ... FROM (multi-table update), DELETE ... USING, and CTEs in DML statements. These features dramatically reduce the number of round trips your application needs.
INSERT — Adding Rows
INSERT adds one or many rows. RETURNING lets you get the generated id or any column back without a separate SELECT. INSERT ... ON CONFLICT implements upsert — insert if the row does not exist, update if it does. This is an atomic operation that avoids race conditions.
-- Single row insert
INSERT INTO shop.customers (email, first_name, last_name)
VALUES ('alice@example.com', 'Alice', 'Smith');
-- Get the generated ID back with RETURNING
INSERT INTO shop.customers (email, first_name, last_name, credit_limit)
VALUES ('bob@example.com', 'Bob', 'Jones', 1000.00)
RETURNING id, email, created_at;
-- Multi-row insert (much faster than many single inserts)
INSERT INTO shop.products (sku, name, price, stock_qty, category)
VALUES
('LAPTOP-001', 'ThinkPad X1', 1299.99, 50, 'Laptops'),
('PHONE-001', 'Pixel 9 Pro', 999.99, 100, 'Phones'),
('TABLET-001', 'iPad Pro 13"', 1099.99, 75, 'Tablets'),
('KB-001', 'Mechanical Keyboard', 149.99, 200, 'Accessories'),
('MOUSE-001', 'Wireless Mouse', 49.99, 300, 'Accessories')
RETURNING id, sku, name;
-- UPSERT — insert or update on conflict
INSERT INTO shop.customers (email, first_name, last_name)
VALUES ('alice@example.com', 'Alice', 'Johnson')
ON CONFLICT (email)
DO UPDATE SET
last_name = EXCLUDED.last_name,
updated_at = now()
RETURNING id, email, last_name;
-- Insert with a subquery
INSERT INTO shop.orders (customer_id, status, total)
SELECT id, 'pending', 0
FROM shop.customers
WHERE email = 'alice@example.com'
RETURNING id, customer_id, status;RETURNING is one of PostgreSQL's most useful non-standard extensions. It eliminates the need for a follow-up SELECT to get generated IDs or computed defaults. You can return any column — RETURNING * returns all columns. This also works with UPDATE and DELETE, making it easy to audit what changed.
UPDATE and DELETE
UPDATE modifies existing rows. Always include a WHERE clause — without it every row in the table is updated! UPDATE ... FROM joins another table to drive the update. DELETE removes rows. TRUNCATE removes all rows much faster than DELETE (not transactional by default). Both support RETURNING.
-- Basic UPDATE
UPDATE shop.products
SET price = price * 1.10 -- 10% price increase
WHERE category = 'Laptops'
RETURNING sku, name, price;
-- UPDATE with a calculation and condition
UPDATE shop.customers
SET
loyalty_points = loyalty_points + 100,
tier = CASE
WHEN loyalty_points + 100 >= 1000 THEN 'gold'
WHEN loyalty_points + 100 >= 500 THEN 'silver'
ELSE 'bronze'
END,
updated_at = now()
WHERE is_active = true
RETURNING id, first_name, loyalty_points, tier;
-- UPDATE using data from another table (UPDATE ... FROM)
UPDATE shop.orders o
SET total = (
SELECT SUM(p.price)
FROM shop.products p
WHERE p.id = o.product_id
)
WHERE o.status = 'pending'
AND o.product_id IS NOT NULL;
-- Soft delete — mark as inactive instead of deleting
UPDATE shop.customers
SET is_active = false,
updated_at = now()
WHERE email = 'bob@example.com'
RETURNING id, email, is_active;
-- Hard DELETE with RETURNING
DELETE FROM shop.orders
WHERE status = 'cancelled'
AND ordered_at < now() - INTERVAL '90 days'
RETURNING id, customer_id, ordered_at;
-- TRUNCATE — removes all rows fast (use with care!)
-- TRUNCATE shop.orders RESTART IDENTITY CASCADE;Wrap destructive operations in a transaction: BEGIN; UPDATE ...; SELECT * FROM ... (verify); COMMIT; or ROLLBACK;. This lets you check the results before permanently applying them. Without BEGIN, each statement auto-commits and cannot be undone.
Advanced Queries
PostgreSQL shines with advanced query features that go far beyond basic SELECT. JOINs combine data from multiple tables. CTEs (WITH clauses) make complex queries readable and composable. Window functions compute running totals, rankings, and moving averages without collapsing rows. Subqueries and EXISTS let you express complex conditions. These features let you do in one query what other languages need multiple round-trips or application logic to achieve.
JOINs and CTEs (Common Table Expressions)
JOINs combine rows from two or more tables. INNER JOIN returns only matching rows. LEFT JOIN returns all rows from the left table plus matching rows from the right (NULL where no match). CTEs (WITH clause) give subqueries a name, making complex queries readable. Recursive CTEs traverse tree structures like org charts and category hierarchies.
-- Sample data setup
INSERT INTO shop.customers (email, first_name, last_name, loyalty_points, tier)
VALUES
('carol@test.com', 'Carol', 'White', 750, 'silver'),
('dave@test.com', 'Dave', 'Brown', 1200, 'gold'),
('eve@test.com', 'Eve', 'Green', 50, 'bronze');
INSERT INTO shop.orders (customer_id, status, total)
VALUES
(1, 'delivered', 1299.99),
(1, 'delivered', 999.99),
(3, 'paid', 149.99),
(4, 'delivered', 1099.99),
(4, 'shipped', 999.99);
-- INNER JOIN — only customers with orders
SELECT
c.first_name || ' ' || c.last_name AS customer,
o.id AS order_id,
o.status,
o.total
FROM shop.customers c
JOIN shop.orders o ON o.customer_id = c.id
ORDER BY c.last_name, o.id;
-- LEFT JOIN — all customers, even those without orders
SELECT
c.first_name,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.total), 0) AS total_spent
FROM shop.customers c
LEFT JOIN shop.orders o ON o.customer_id = c.id
GROUP BY c.id, c.first_name
ORDER BY total_spent DESC;
-- CTE — readable step-by-step query
WITH
customer_totals AS (
SELECT
c.id,
c.first_name || ' ' || c.last_name AS name,
c.tier,
COUNT(o.id) AS orders,
SUM(o.total) AS revenue
FROM shop.customers c
JOIN shop.orders o ON o.customer_id = c.id
WHERE o.status IN ('paid','delivered')
GROUP BY c.id, c.first_name, c.last_name, c.tier
),
ranked AS (
SELECT
*,
RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
FROM customer_totals
)
SELECT * FROM ranked WHERE revenue_rank <= 3;CTEs are not just syntactic sugar — they can be materialised (computed once and cached) with WITH ... AS MATERIALIZED or computed inline with NOT MATERIALIZED. PostgreSQL 12+ uses NOT MATERIALIZED by default for better performance. Use MATERIALIZED when you want to avoid re-running an expensive subquery.
Window Functions
Window functions compute a value across a set of rows related to the current row, without collapsing them into groups. Unlike GROUP BY + aggregate, the original rows are preserved. OVER() defines the window. PARTITION BY splits into groups. ORDER BY defines the order within the window. ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM, AVG are common window functions.
-- Setup some order data with dates
INSERT INTO shop.orders (customer_id, status, total, ordered_at)
VALUES
(1, 'delivered', 299.99, '2026-01-15'),
(1, 'delivered', 599.99, '2026-02-20'),
(1, 'delivered', 199.99, '2026-03-10'),
(3, 'delivered', 499.99, '2026-01-25'),
(3, 'delivered', 899.99, '2026-02-14'),
(4, 'delivered', 349.99, '2026-03-05');
-- ROW_NUMBER — sequential number per group
-- RANK — rank with gaps on ties
-- DENSE_RANK — rank without gaps
SELECT
c.first_name,
o.total,
ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY o.total DESC) AS row_num,
RANK() OVER (PARTITION BY c.id ORDER BY o.total DESC) AS rank,
DENSE_RANK() OVER (PARTITION BY c.id ORDER BY o.total DESC) AS dense_rank
FROM shop.customers c
JOIN shop.orders o ON o.customer_id = c.id
WHERE o.status = 'delivered'
ORDER BY c.first_name, o.total DESC;
-- Running total and moving average
SELECT
DATE_TRUNC('month', o.ordered_at)::date AS month,
o.total,
SUM(o.total) OVER (ORDER BY o.ordered_at
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total,
AVG(o.total) OVER (ORDER BY o.ordered_at
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3
FROM shop.orders o
WHERE o.status = 'delivered'
ORDER BY o.ordered_at;
-- LAG and LEAD — compare to previous/next row
SELECT
DATE_TRUNC('month', ordered_at)::date AS month,
SUM(total) AS revenue,
LAG(SUM(total)) OVER (ORDER BY DATE_TRUNC('month', ordered_at)) AS prev_month,
SUM(total) - LAG(SUM(total)) OVER (ORDER BY DATE_TRUNC('month', ordered_at)) AS change
FROM shop.orders
WHERE status = 'delivered'
GROUP BY DATE_TRUNC('month', ordered_at)
ORDER BY month;Window functions are one of PostgreSQL's most powerful features and are absent or limited in MySQL. ROWS BETWEEN defines the frame within the window: UNBOUNDED PRECEDING to CURRENT ROW gives a running total. PRECEDING 2 to CURRENT ROW gives a 3-row moving average. UNBOUNDED PRECEDING to UNBOUNDED FOLLOWING covers the entire partition.
JSON & Full-Text Search
PostgreSQL combines the best of relational and document databases. JSONB (binary JSON) stores semi-structured data with full indexing support — you can query deeply nested JSON fields just like regular columns. Full-text search with tsvector and tsquery provides Google-style text search built directly into the database, no Elasticsearch required for most use cases.
JSONB — Querying Semi-Structured Data
JSONB stores JSON in a parsed binary format. Use -> to extract a JSON object by key (returns JSON), ->> to extract as text, #> for nested paths. The @> operator checks containment. ? checks key existence. GIN indexes make JSONB queries extremely fast even on millions of rows.
-- Table with JSONB column
CREATE TABLE products_ext (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
metadata JSONB
);
INSERT INTO products_ext (name, metadata) VALUES
('MacBook Pro', '{
"brand": "Apple",
"specs": {"cpu": "M3 Pro", "ram_gb": 36, "storage_tb": 1},
"tags": ["laptop", "premium", "apple"],
"colors": ["space-black", "silver"],
"in_stock": true
}'),
('Dell XPS 15', '{
"brand": "Dell",
"specs": {"cpu": "Intel i9", "ram_gb": 32, "storage_tb": 2},
"tags": ["laptop", "windows"],
"colors": ["platinum-silver"],
"in_stock": true
}'),
('iPad Pro', '{
"brand": "Apple",
"specs": {"cpu": "M4", "ram_gb": 16, "storage_tb": 0.5},
"tags": ["tablet", "premium", "apple"],
"in_stock": false
}');
-- Extract fields with -> and ->>
SELECT
name,
metadata->>'brand' AS brand,
metadata->'specs'->>'cpu' AS cpu,
(metadata->'specs'->>'ram_gb')::int AS ram_gb,
metadata->'in_stock' AS in_stock
FROM products_ext;
-- Filter on JSONB fields
SELECT name FROM products_ext
WHERE metadata->>'brand' = 'Apple'
AND (metadata->'specs'->>'ram_gb')::int >= 32;
-- Containment operator @>
SELECT name FROM products_ext
WHERE metadata @> '{"brand": "Apple"}';
-- Array containment — has 'laptop' tag
SELECT name FROM products_ext
WHERE metadata->'tags' @> '["laptop"]';
-- Update JSONB — add a field
UPDATE products_ext
SET metadata = metadata || '{"discount": 0.15}'::jsonb
WHERE name = 'MacBook Pro'
RETURNING name, metadata->>'discount' AS discount;
-- GIN index for fast JSONB queries
CREATE INDEX idx_products_metadata ON products_ext USING GIN (metadata);JSONB vs JSON: always use JSONB unless you need to preserve key order or duplicate keys (rare). JSONB is stored in parsed binary form — it strips whitespace, deduplicates keys, and supports GIN indexing. JSON stores the raw text and re-parses on every access. JSONB is almost always faster for queries.
Full-Text Search with tsvector
Full-text search parses text into lexemes (normalised word roots), strips stop words (the, is, a), and builds an inverted index. tsvector is the indexed document representation. tsquery is the search query. The @@ operator checks if a query matches a document. to_tsvector and to_tsquery handle parsing and normalisation.
-- Create a table for articles
CREATE TABLE articles (
id BIGSERIAL PRIMARY KEY,
title TEXT NOT NULL,
body TEXT NOT NULL,
author TEXT NOT NULL,
ts_vector TSVECTOR GENERATED ALWAYS AS (
to_tsvector('english', title || ' ' || body)
) STORED
);
-- GIN index on the tsvector column
CREATE INDEX idx_articles_fts ON articles USING GIN (ts_vector);
INSERT INTO articles (title, body, author) VALUES
('Getting Started with PostgreSQL',
'PostgreSQL is a powerful open-source relational database. It supports full SQL compliance and ACID transactions. Great for web applications.',
'Alice'),
('Advanced SQL Window Functions',
'Window functions compute values across related rows without collapsing them. RANK, ROW_NUMBER, and LAG are common examples.',
'Bob'),
('JSON Support in PostgreSQL',
'PostgreSQL supports both JSON and JSONB data types. JSONB is binary and faster for querying. Use GIN indexes for performance.',
'Carol'),
('Building REST APIs with Node.js and PostgreSQL',
'Connect your Node.js application to PostgreSQL using the pg library. Use parameterised queries to prevent SQL injection.',
'Dave');
-- Basic full-text search
SELECT title, author
FROM articles
WHERE ts_vector @@ to_tsquery('english', 'PostgreSQL');
-- Multi-word search: & = AND, | = OR, ! = NOT
SELECT title
FROM articles
WHERE ts_vector @@ to_tsquery('english', 'window & function');
-- Phrase search (adjacent words)
SELECT title
FROM articles
WHERE ts_vector @@ phraseto_tsquery('english', 'full text search');
-- Ranking results by relevance
SELECT
title,
author,
ts_rank(ts_vector, query) AS rank
FROM articles,
to_tsquery('english', 'PostgreSQL | SQL') query
WHERE ts_vector @@ query
ORDER BY rank DESC;
-- Headline — highlight matching terms
SELECT
title,
ts_headline('english', body,
to_tsquery('english', 'PostgreSQL'),
'MaxWords=20, MinWords=5'
) AS excerpt
FROM articles
WHERE ts_vector @@ to_tsquery('english', 'PostgreSQL');The GENERATED ALWAYS AS ... STORED column automatically updates the tsvector whenever title or body changes — you never have to maintain it manually. For multi-language support, pass the language as a column: to_tsvector(language_column::regconfig, body). English, French, German, Spanish and many others are built in.
Transactions & Performance
PostgreSQL is fully ACID-compliant — transactions guarantee Atomicity (all or nothing), Consistency (valid state), Isolation (concurrent transactions do not interfere), and Durability (committed data survives crashes). EXPLAIN ANALYZE reveals exactly how the query planner executes your query so you can identify and fix slow queries with the right indexes.
Transactions and Isolation Levels
Wrap related operations in a transaction with BEGIN ... COMMIT. ROLLBACK undoes everything since BEGIN. SAVEPOINT creates a checkpoint inside a transaction — you can rollback to a savepoint without aborting the whole transaction. PostgreSQL supports four isolation levels: READ COMMITTED (default), REPEATABLE READ, SERIALIZABLE.
-- Basic transaction
BEGIN;
UPDATE shop.customers
SET loyalty_points = loyalty_points - 500
WHERE id = 1
AND loyalty_points >= 500;
-- Check if update succeeded (1 row affected = enough points)
-- Application would check rows_affected here
INSERT INTO shop.orders (customer_id, status, total)
VALUES (1, 'paid', 49.99);
COMMIT;
-- Transaction with SAVEPOINT
BEGIN;
INSERT INTO shop.customers (email, first_name, last_name)
VALUES ('test1@example.com', 'Test', 'One');
SAVEPOINT after_first_insert;
INSERT INTO shop.customers (email, first_name, last_name)
VALUES ('test2@example.com', 'Test', 'Two');
-- Oops — rollback only the second insert
ROLLBACK TO SAVEPOINT after_first_insert;
-- First insert is still in the transaction
INSERT INTO shop.customers (email, first_name, last_name)
VALUES ('test3@example.com', 'Test', 'Three');
COMMIT;
-- Verify — should have test1 and test3, not test2
SELECT email FROM shop.customers
WHERE email LIKE 'test%'
ORDER BY email;
-- Serializable isolation — prevents phantom reads
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(total) FROM shop.orders WHERE customer_id = 1;
-- Other transactions cannot insert orders for customer 1
-- until this transaction commits or rolls back
COMMIT;PostgreSQL's default isolation level (READ COMMITTED) means each statement in a transaction sees data committed before that statement began. This prevents dirty reads. Use REPEATABLE READ to prevent non-repeatable reads (same SELECT giving different results within a transaction). Use SERIALIZABLE for full correctness in financial systems.
EXPLAIN ANALYZE — Query Performance
EXPLAIN shows the query plan without executing. EXPLAIN ANALYZE actually runs the query and shows real timings. Key metrics: cost (in arbitrary planner units), rows (estimated vs actual), loops (how many times a node ran), buffers (cache hits vs disk reads). Sequential scans on large tables are slow — the right index changes them to fast index scans.
-- Create a larger dataset for demo
INSERT INTO shop.orders (customer_id, status, total, ordered_at)
SELECT
(RANDOM() * 4 + 1)::INT,
(ARRAY['pending','paid','shipped','delivered','cancelled'])[CEIL(RANDOM() * 5)],
ROUND((RANDOM() * 1000 + 10)::NUMERIC, 2),
NOW() - (RANDOM() * INTERVAL '365 days')
FROM generate_series(1, 10000);
-- EXPLAIN — show plan without running
EXPLAIN
SELECT *
FROM shop.orders
WHERE status = 'delivered'
AND total > 500;
-- EXPLAIN ANALYZE — run and show real timings
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
c.first_name,
COUNT(*) AS orders,
SUM(o.total) AS revenue
FROM shop.customers c
JOIN shop.orders o ON o.customer_id = c.id
WHERE o.status = 'delivered'
GROUP BY c.id, c.first_name
ORDER BY revenue DESC;
-- Add missing index and compare
CREATE INDEX idx_orders_status_total
ON shop.orders (status, total);
-- Re-run to see index scan instead of seq scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM shop.orders
WHERE status = 'delivered' AND total > 500;
-- Check index usage across the database
SELECT
schemaname,
tablename,
indexname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;Rules of thumb for indexes: add an index on every foreign key column (Postgres does NOT do this automatically). Index columns used in WHERE, JOIN ON, and ORDER BY. Composite indexes (status, total) work for queries that filter on status alone OR status + total, but NOT total alone. Use EXPLAIN ANALYZE before and after every schema change.
You finished the PostgreSQL tutorial!
You now know PostgreSQL's data types, DDL, DML, advanced queries, JSON, full-text search, transactions, and performance tuning — enough to build and optimise production databases.