PostgreSQL Tutorial for Beginners (2025): Learn PostgreSQL Step by Step

PostgreSQL is the world's most advanced open-source relational database. It's trusted by Apple, Instagram, Spotify, Reddit, and thousands of companies for its reliability, standards compliance, and powerful features. This tutorial takes you from zero to building real PostgreSQL databases — no prior experience required.

Why Learn PostgreSQL?

PostgreSQL (often called "Postgres") consistently tops developer surveys as the most loved database. Here's why it's worth learning:

Use cases: Web apps, analytics, data warehousing, GIS (PostGIS), time-series, full-text search, JSON document storage.

PostgreSQL vs MySQL vs SQLite

Setup: Install PostgreSQL

Windows

1. Download installer from postgresql.org/download/windows
2. Run the installer (includes pgAdmin and psql)
3. Remember the password you set for the postgres superuser
4. Add PostgreSQL to PATH: C:\Program Files\PostgreSQL\16\bin

# Verify installation
psql --version
# PostgreSQL 16.x

macOS

# Option 1: Homebrew (recommended)
brew install postgresql@16
brew services start postgresql@16
echo 'export PATH="/opt/homebrew/opt/postgresql@16/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

# Option 2: Postgres.app (GUI app)
# Download from postgresapp.com

Linux (Ubuntu/Debian)

sudo apt update
sudo apt install postgresql postgresql-contrib

# Start service
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Switch to postgres user
sudo -u postgres psql

Docker (any OS — recommended for development)

docker run --name mypostgres \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_USER=myuser \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  -d postgres:16

# Connect
docker exec -it mypostgres psql -U myuser -d mydb

Connect with psql

# Local connection
psql -U postgres

# With database
psql -U postgres -d mydb

# Remote connection
psql -h hostname -p 5432 -U myuser -d mydb

# Connection string
psql postgresql://myuser:secret@localhost:5432/mydb

GUI Tools

psql Basics

The psql command-line tool is your best friend for PostgreSQL.

-- psql meta-commands (start with \)
\l           -- list databases
\c mydb      -- connect to database
\dt          -- list tables
\d tablename -- describe table
\du          -- list users/roles
\i file.sql  -- run SQL file
\q           -- quit
\?           -- help for meta-commands
\h SELECT    -- help for SQL command

Databases and Schemas

-- Create a database
CREATE DATABASE myapp;

-- Connect to it
\c myapp

-- Create a schema (namespace for tables)
CREATE SCHEMA products;

-- Set default schema
SET search_path TO products, public;

-- Drop database (disconnect all users first)
DROP DATABASE IF EXISTS myapp;

Schema vs Database: A database contains multiple schemas. Schemas group related tables. The default schema is public.

Data Types

PostgreSQL has the most comprehensive type system of any SQL database:

Numeric Types

Text Types

PostgreSQL tip: Use TEXT instead of VARCHAR(255). PostgreSQL stores them identically — TEXT is simpler.

Date/Time Types

Always use TIMESTAMPTZ for storing datetimes — it stores UTC and converts to your timezone automatically.

Boolean

-- TRUE values: true, 't', 'yes', 'on', 1
-- FALSE values: false, 'f', 'no', 'off', 0
status BOOLEAN DEFAULT TRUE

PostgreSQL-Specific Types

Creating Tables

-- Basic table
CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    email       TEXT NOT NULL UNIQUE,
    username    VARCHAR(50) NOT NULL,
    password    TEXT NOT NULL,
    first_name  TEXT,
    last_name   TEXT,
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    updated_at  TIMESTAMPTZ DEFAULT NOW(),
    is_active   BOOLEAN DEFAULT TRUE
);

-- Table with foreign key
CREATE TABLE posts (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title       TEXT NOT NULL,
    slug        TEXT NOT NULL UNIQUE,
    content     TEXT,
    status      TEXT DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
    published_at TIMESTAMPTZ,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- Many-to-many join table
CREATE TABLE post_tags (
    post_id     BIGINT REFERENCES posts(id) ON DELETE CASCADE,
    tag_id      BIGINT REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

Modern: Use IDENTITY Instead of SERIAL

-- Preferred in PostgreSQL 10+
CREATE TABLE products (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        TEXT NOT NULL,
    price       NUMERIC(10, 2) NOT NULL CHECK (price >= 0)
);

INSERT — Adding Data

-- Single row
INSERT INTO users (email, username, password)
VALUES ('alice@example.com', 'alice', 'hashed_password');

-- Multiple rows
INSERT INTO users (email, username, password)
VALUES
    ('bob@example.com', 'bob', 'hash1'),
    ('carol@example.com', 'carol', 'hash2'),
    ('dave@example.com', 'dave', 'hash3');

-- Return inserted row (PostgreSQL-specific)
INSERT INTO users (email, username, password)
VALUES ('eve@example.com', 'eve', 'hash4')
RETURNING id, email, created_at;

-- Insert or do nothing on conflict
INSERT INTO users (email, username, password)
VALUES ('alice@example.com', 'alice2', 'hash5')
ON CONFLICT (email) DO NOTHING;

-- Upsert: insert or update on conflict
INSERT INTO users (email, username, password)
VALUES ('alice@example.com', 'alice', 'new_hash')
ON CONFLICT (email) DO UPDATE
    SET password = EXCLUDED.password,
        updated_at = NOW();

SELECT — Querying Data

-- Basic select
SELECT * FROM users;

-- Specific columns
SELECT id, email, username FROM users;

-- With alias
SELECT
    id,
    email,
    username AS name,
    created_at AS joined
FROM users;

-- Filter with WHERE
SELECT * FROM users WHERE is_active = TRUE;

-- Multiple conditions
SELECT * FROM users
WHERE is_active = TRUE
  AND created_at > '2025-01-01';

-- OR condition
SELECT * FROM users
WHERE username = 'alice' OR username = 'bob';

-- IN list
SELECT * FROM users
WHERE username IN ('alice', 'bob', 'carol');

-- Pattern matching
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE email ILIKE '%GMAIL%'; -- case-insensitive

-- NULL checks
SELECT * FROM users WHERE last_name IS NULL;
SELECT * FROM users WHERE last_name IS NOT NULL;

-- BETWEEN
SELECT * FROM users
WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31';

-- Sort
SELECT * FROM users ORDER BY username ASC;
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY last_name ASC NULLS LAST;

-- Limit and offset (pagination)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

UPDATE — Modifying Data

-- Update a single column
UPDATE users SET is_active = FALSE WHERE id = 5;

-- Update multiple columns
UPDATE users
SET
    first_name = 'Alice',
    last_name  = 'Smith',
    updated_at = NOW()
WHERE id = 1;

-- Update with RETURNING
UPDATE users
SET is_active = FALSE
WHERE created_at < '2024-01-01'
RETURNING id, email;

-- Update based on another table
UPDATE posts
SET status = 'archived'
WHERE user_id IN (
    SELECT id FROM users WHERE is_active = FALSE
);

DELETE — Removing Data

-- Delete specific row
DELETE FROM users WHERE id = 5;

-- Delete with condition
DELETE FROM posts WHERE status = 'draft' AND created_at < NOW() - INTERVAL '30 days';

-- Delete all rows (use TRUNCATE for better performance)
DELETE FROM sessions;
TRUNCATE TABLE sessions;              -- faster, resets sequences too
TRUNCATE TABLE sessions RESTART IDENTITY;  -- explicit reset

-- Delete with RETURNING
DELETE FROM users WHERE is_active = FALSE RETURNING id, email;

Aggregation Functions

-- Count
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users WHERE is_active = TRUE;
SELECT COUNT(DISTINCT username) FROM users;  -- distinct values

-- Sum, Average, Min, Max
SELECT
    SUM(price)    AS total,
    AVG(price)    AS average,
    MIN(price)    AS cheapest,
    MAX(price)    AS most_expensive
FROM products;

-- Round
SELECT ROUND(AVG(price), 2) AS avg_price FROM products;

-- GROUP BY
SELECT status, COUNT(*) AS post_count
FROM posts
GROUP BY status;

-- HAVING (filter after grouping)
SELECT user_id, COUNT(*) AS post_count
FROM posts
GROUP BY user_id
HAVING COUNT(*) > 5
ORDER BY post_count DESC;

-- Multiple aggregates with GROUP BY
SELECT
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS new_users,
    COUNT(*) FILTER (WHERE is_active = TRUE) AS active_users
FROM users
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

JOINs

-- INNER JOIN: only matching rows
SELECT u.username, p.title, p.created_at
FROM users u
INNER JOIN posts p ON p.user_id = u.id;

-- LEFT JOIN: all users, even with no posts
SELECT u.username, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.username
ORDER BY post_count DESC;

-- RIGHT JOIN: all posts, even with no user (unusual)
SELECT u.username, p.title
FROM users u
RIGHT JOIN posts p ON p.user_id = u.id;

-- FULL OUTER JOIN: all from both tables
SELECT u.username, p.title
FROM users u
FULL OUTER JOIN posts p ON p.user_id = u.id;

-- Join three tables
SELECT
    u.username,
    p.title,
    t.name AS tag
FROM posts p
JOIN users u ON u.id = p.user_id
JOIN post_tags pt ON pt.post_id = p.id
JOIN tags t ON t.id = pt.tag_id
WHERE p.status = 'published';

-- Self join (e.g., employees with their manager)
SELECT
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

JOIN Types Summary

Subqueries and CTEs

Subqueries

-- Scalar subquery
SELECT username,
       (SELECT COUNT(*) FROM posts WHERE user_id = u.id) AS post_count
FROM users u;

-- Subquery in WHERE
SELECT * FROM posts
WHERE user_id IN (
    SELECT id FROM users WHERE created_at > '2025-01-01'
);

-- EXISTS
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM posts p WHERE p.user_id = u.id AND p.status = 'published'
);

-- NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM posts p WHERE p.user_id = u.id
);

CTEs (Common Table Expressions)

-- Basic CTE
WITH active_users AS (
    SELECT * FROM users WHERE is_active = TRUE
)
SELECT * FROM active_users WHERE created_at > '2025-01-01';

-- Multiple CTEs
WITH
    recent_users AS (
        SELECT id, username FROM users
        WHERE created_at > NOW() - INTERVAL '30 days'
    ),
    recent_posts AS (
        SELECT user_id, COUNT(*) AS post_count
        FROM posts
        WHERE created_at > NOW() - INTERVAL '30 days'
        GROUP BY user_id
    )
SELECT u.username, COALESCE(p.post_count, 0) AS posts
FROM recent_users u
LEFT JOIN recent_posts p ON p.user_id = u.id;

-- Recursive CTE (for hierarchical data)
WITH RECURSIVE category_tree AS (
    -- Base case: root categories
    SELECT id, name, parent_id, 0 AS level
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive case: children
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    JOIN category_tree ct ON ct.id = c.parent_id
)
SELECT * FROM category_tree ORDER BY level, name;

Window Functions

Window functions are one of PostgreSQL's greatest strengths:

-- ROW_NUMBER: unique rank per row
SELECT
    username,
    created_at,
    ROW_NUMBER() OVER (ORDER BY created_at) AS join_order
FROM users;

-- RANK: same rank for ties, gaps after ties
-- DENSE_RANK: same rank for ties, no gaps
SELECT
    username,
    post_count,
    RANK()       OVER (ORDER BY post_count DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY post_count DESC) AS dense_rank
FROM (
    SELECT username, COUNT(p.id) AS post_count
    FROM users u LEFT JOIN posts p ON p.user_id = u.id
    GROUP BY u.id, username
) sub;

-- LAG/LEAD: access previous/next row
SELECT
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS signups,
    LAG(COUNT(*)) OVER (ORDER BY DATE_TRUNC('month', created_at)) AS prev_month,
    COUNT(*) - LAG(COUNT(*)) OVER (ORDER BY DATE_TRUNC('month', created_at)) AS growth
FROM users
GROUP BY DATE_TRUNC('month', created_at);

-- Running total
SELECT
    created_at::DATE AS date,
    COUNT(*) AS daily_signups,
    SUM(COUNT(*)) OVER (ORDER BY created_at::DATE) AS cumulative
FROM users
GROUP BY created_at::DATE;

-- PARTITION BY: reset window per group
SELECT
    category,
    product_name,
    price,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank_in_category
FROM products;

-- NTILE: divide into buckets
SELECT
    username,
    post_count,
    NTILE(4) OVER (ORDER BY post_count) AS quartile
FROM (
    SELECT username, COUNT(p.id) AS post_count
    FROM users u LEFT JOIN posts p ON p.user_id = u.id
    GROUP BY u.id, username
) sub;

String Functions

-- Length
SELECT LENGTH('hello');           -- 5
SELECT CHAR_LENGTH('héllo');      -- 5 (characters, not bytes)

-- Case
SELECT UPPER('hello');            -- HELLO
SELECT LOWER('HELLO');            -- hello
SELECT INITCAP('hello world');    -- Hello World

-- Trim
SELECT TRIM('  hello  ');         -- 'hello'
SELECT LTRIM('  hello  ');        -- 'hello  '
SELECT RTRIM('  hello  ');        -- '  hello'

-- Substring
SELECT SUBSTRING('hello world', 1, 5);    -- 'hello'
SELECT LEFT('hello world', 5);            -- 'hello'
SELECT RIGHT('hello world', 5);           -- 'world'

-- Replace
SELECT REPLACE('hello world', 'world', 'postgres');  -- 'hello postgres'

-- Concatenate
SELECT 'hello' || ' ' || 'world';         -- 'hello world'
SELECT CONCAT('hello', ' ', 'world');     -- 'hello world'
SELECT CONCAT_WS(', ', 'Alice', 'Bob', 'Carol');  -- 'Alice, Bob, Carol'

-- Split
SELECT SPLIT_PART('alice@example.com', '@', 1);  -- 'alice'
SELECT SPLIT_PART('alice@example.com', '@', 2);  -- 'example.com'

-- String formatting
SELECT FORMAT('Hello, %s! You are %s years old.', 'Alice', 30);

-- Regex
SELECT REGEXP_REPLACE('Hello World', '\s+', '_', 'g');  -- 'Hello_World'
SELECT REGEXP_MATCHES('2025-01-15', '(\d{4})-(\d{2})-(\d{2})');

Date and Time Functions

-- Current timestamp
SELECT NOW();                 -- 2025-01-15 14:30:00+00
SELECT CURRENT_TIMESTAMP;     -- same
SELECT CURRENT_DATE;          -- 2025-01-15
SELECT CURRENT_TIME;          -- 14:30:00+00

-- Extract parts
SELECT EXTRACT(YEAR   FROM NOW());   -- 2025
SELECT EXTRACT(MONTH  FROM NOW());   -- 1
SELECT EXTRACT(DAY    FROM NOW());   -- 15
SELECT EXTRACT(HOUR   FROM NOW());   -- 14
SELECT EXTRACT(DOW    FROM NOW());   -- 3 (Wednesday, 0=Sunday)
SELECT EXTRACT(EPOCH  FROM NOW());   -- Unix timestamp

-- Truncate to period
SELECT DATE_TRUNC('day',   NOW());   -- 2025-01-15 00:00:00+00
SELECT DATE_TRUNC('month', NOW());   -- 2025-01-01 00:00:00+00
SELECT DATE_TRUNC('year',  NOW());   -- 2025-01-01 00:00:00+00
SELECT DATE_TRUNC('hour',  NOW());   -- 2025-01-15 14:00:00+00

-- Arithmetic with intervals
SELECT NOW() + INTERVAL '7 days';
SELECT NOW() - INTERVAL '1 month';
SELECT NOW() + INTERVAL '2 hours 30 minutes';

-- Age
SELECT AGE(NOW(), '1990-05-15');     -- 34 years 8 months 0 days
SELECT AGE('2025-01-15', '1990-05-15');

-- Format
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');  -- '2025-01-15 14:30:00'
SELECT TO_CHAR(NOW(), 'Month DD, YYYY');           -- 'January 15, 2025'

-- Convert timezone
SELECT NOW() AT TIME ZONE 'America/New_York';

Indexes

Indexes dramatically speed up queries on large tables:

-- Default: B-tree index (for =, <, >, BETWEEN, LIKE 'prefix%')
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_status ON posts(status);

-- Unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Composite index (for queries filtering on multiple columns)
CREATE INDEX idx_posts_user_status ON posts(user_id, status);

-- Partial index (indexes only some rows — very efficient)
CREATE INDEX idx_active_users ON users(email) WHERE is_active = TRUE;
CREATE INDEX idx_published_posts ON posts(created_at) WHERE status = 'published';

-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- Now this query uses the index:
-- SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- GIN index (for JSONB, arrays, full-text search)
CREATE INDEX idx_posts_content_fts ON posts USING GIN(to_tsvector('english', content));
CREATE INDEX idx_users_metadata ON users USING GIN(metadata);  -- JSONB column

-- Drop index
DROP INDEX IF EXISTS idx_users_email;

When to Add Indexes

EXPLAIN and EXPLAIN ANALYZE

-- See query plan (no execution)
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

-- Execute and show actual timings
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

-- Full details
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM users WHERE email = 'alice@example.com';

Key terms in EXPLAIN output:

Transactions

Transactions ensure data integrity with ACID guarantees:

-- Basic transaction
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Rollback on error
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    -- Something went wrong
ROLLBACK;

-- Savepoints (partial rollback)
BEGIN;
    INSERT INTO orders (user_id, total) VALUES (1, 99.99) RETURNING id;
    SAVEPOINT order_created;

    INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 5, 2);
    -- This failed, roll back to savepoint
    ROLLBACK TO SAVEPOINT order_created;

    -- Try different approach
    INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 5, 1);
COMMIT;

Isolation Levels

PostgreSQL's REPEATABLE READ also prevents phantom reads (better than SQL standard requires).

-- Set isolation level
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... your queries ...
COMMIT;

Views

-- Create a view
CREATE VIEW active_users_view AS
    SELECT id, email, username, created_at
    FROM users
    WHERE is_active = TRUE;

-- Use the view like a table
SELECT * FROM active_users_view;

-- Updatable view (if based on single table, no aggregation)
UPDATE active_users_view SET username = 'alice2' WHERE id = 1;

-- Materialized view (stores results on disk — refresh manually)
CREATE MATERIALIZED VIEW monthly_stats AS
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        COUNT(*) AS user_count,
        COUNT(*) FILTER (WHERE is_active) AS active_count
    FROM users
    GROUP BY DATE_TRUNC('month', created_at);

-- Refresh materialized view
REFRESH MATERIALIZED VIEW monthly_stats;

-- Refresh without locking reads
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_stats;

-- Drop view
DROP VIEW IF EXISTS active_users_view;
DROP MATERIALIZED VIEW IF EXISTS monthly_stats;

JSON and JSONB

PostgreSQL's JSON support is industry-leading:

-- Create a table with JSONB column
CREATE TABLE user_profiles (
    user_id  BIGINT PRIMARY KEY REFERENCES users(id),
    metadata JSONB NOT NULL DEFAULT '{}'
);

-- Insert JSON
INSERT INTO user_profiles (user_id, metadata)
VALUES (1, '{"age": 30, "city": "New York", "tags": ["admin", "beta"]}');

-- Query JSON fields
SELECT metadata->>'city' AS city FROM user_profiles;          -- text output
SELECT metadata->'age' AS age FROM user_profiles;             -- JSON output (numeric)
SELECT (metadata->>'age')::INT AS age FROM user_profiles;     -- cast to integer

-- Nested access
SELECT metadata->'address'->>'street' FROM user_profiles;

-- Array access
SELECT metadata->'tags'->0 AS first_tag FROM user_profiles;  -- "admin"

-- Filter by JSON field
SELECT * FROM user_profiles WHERE metadata->>'city' = 'New York';
SELECT * FROM user_profiles WHERE (metadata->>'age')::INT > 25;

-- Contains operator (@>)
SELECT * FROM user_profiles WHERE metadata @> '{"city": "New York"}';

-- Check if key exists
SELECT * FROM user_profiles WHERE metadata ? 'age';
SELECT * FROM user_profiles WHERE metadata ?| ARRAY['age', 'city'];  -- any key

-- Update JSON field
UPDATE user_profiles
SET metadata = metadata || '{"city": "Boston"}'
WHERE user_id = 1;

-- Remove key
UPDATE user_profiles
SET metadata = metadata - 'age'
WHERE user_id = 1;

-- jsonb_set
UPDATE user_profiles
SET metadata = jsonb_set(metadata, '{address,city}', '"Boston"', true)
WHERE user_id = 1;

-- Expand JSON array
SELECT user_id, jsonb_array_elements_text(metadata->'tags') AS tag
FROM user_profiles;

-- Aggregate into JSON
SELECT jsonb_agg(jsonb_build_object('id', id, 'name', username))
FROM users WHERE is_active = TRUE;

Arrays

-- Create a table with array columns
CREATE TABLE products (
    id       BIGSERIAL PRIMARY KEY,
    name     TEXT NOT NULL,
    tags     TEXT[],
    prices   NUMERIC[]
);

-- Insert arrays
INSERT INTO products (name, tags, prices)
VALUES ('Widget', ARRAY['sale', 'new', 'popular'], ARRAY[9.99, 14.99, 19.99]);

-- Access array elements (1-indexed!)
SELECT tags[1] FROM products;           -- 'sale'
SELECT tags[1:2] FROM products;         -- '{sale,new}'

-- Array operators
SELECT * FROM products WHERE 'sale' = ANY(tags);      -- contains 'sale'
SELECT * FROM products WHERE tags @> ARRAY['sale'];    -- contains all
SELECT * FROM products WHERE tags && ARRAY['sale', 'new'];  -- overlap (any)

-- Array functions
SELECT array_length(tags, 1) FROM products;  -- number of elements
SELECT unnest(tags) FROM products;           -- expand to rows
SELECT array_append(tags, 'clearance') FROM products;
SELECT array_remove(tags, 'sale') FROM products;

-- Array aggregation
SELECT user_id, array_agg(title) AS post_titles
FROM posts
GROUP BY user_id;

Full-Text Search

-- Create a full-text search index
CREATE INDEX idx_posts_fts ON posts
    USING GIN(to_tsvector('english', title || ' ' || COALESCE(content, '')));

-- Search
SELECT title, ts_rank(to_tsvector('english', content), query) AS rank
FROM posts,
     to_tsquery('english', 'postgresql & database') query
WHERE to_tsvector('english', title || ' ' || content) @@ query
ORDER BY rank DESC;

-- Using a stored tsvector column (better performance)
ALTER TABLE posts ADD COLUMN tsv TSVECTOR;

UPDATE posts
SET tsv = to_tsvector('english', title || ' ' || COALESCE(content, ''));

CREATE INDEX idx_posts_tsv ON posts USING GIN(tsv);

-- Trigger to auto-update tsv
CREATE FUNCTION posts_tsv_trigger() RETURNS TRIGGER AS $$
BEGIN
    NEW.tsv := to_tsvector('english',
        COALESCE(NEW.title, '') || ' ' || COALESCE(NEW.content, ''));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_posts_tsv
    BEFORE INSERT OR UPDATE ON posts
    FOR EACH ROW EXECUTE FUNCTION posts_tsv_trigger();

Stored Functions and Procedures

-- Function (returns a value)
CREATE OR REPLACE FUNCTION get_user_post_count(user_id_param BIGINT)
RETURNS INTEGER AS $$
    SELECT COUNT(*)::INTEGER FROM posts WHERE user_id = user_id_param;
$$ LANGUAGE sql;

-- Use it
SELECT username, get_user_post_count(id) AS posts
FROM users;

-- PL/pgSQL function (more powerful)
CREATE OR REPLACE FUNCTION create_user_with_profile(
    p_email    TEXT,
    p_username TEXT,
    p_password TEXT,
    p_city     TEXT DEFAULT NULL
) RETURNS BIGINT AS $$
DECLARE
    v_user_id BIGINT;
BEGIN
    INSERT INTO users (email, username, password)
    VALUES (p_email, p_username, p_password)
    RETURNING id INTO v_user_id;

    INSERT INTO user_profiles (user_id, metadata)
    VALUES (v_user_id, jsonb_build_object('city', p_city));

    RETURN v_user_id;
END;
$$ LANGUAGE plpgsql;

-- Call it
SELECT create_user_with_profile('frank@example.com', 'frank', 'hash', 'Chicago');

-- Procedure (no return value, supports COMMIT inside)
CREATE OR REPLACE PROCEDURE archive_old_posts(days_old INTEGER DEFAULT 365)
AS $$
BEGIN
    UPDATE posts
    SET status = 'archived'
    WHERE status = 'published'
      AND created_at < NOW() - (days_old || ' days')::INTERVAL;

    RAISE NOTICE 'Archived % posts', ROW_COUNT;
END;
$$ LANGUAGE plpgsql;

-- Call procedure
CALL archive_old_posts(180);

Triggers

-- Auto-update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Audit log trigger
CREATE TABLE audit_log (
    id         BIGSERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    operation  TEXT NOT NULL,
    old_data   JSONB,
    new_data   JSONB,
    changed_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, operation, old_data, new_data)
    VALUES (
        TG_TABLE_NAME,
        TG_OP,
        CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN row_to_json(OLD)::JSONB END,
        CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN row_to_json(NEW)::JSONB END
    );
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_users
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW EXECUTE FUNCTION audit_trigger();

User Management and Security

-- Create a user/role
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password';

-- Grant privileges
GRANT CONNECT ON DATABASE myapp TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Read-only user
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'read_password';
GRANT CONNECT ON DATABASE myapp TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

-- Revoke privileges
REVOKE DELETE ON users FROM app_user;

-- Row Level Security (RLS) — users see only their own data
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_posts_policy ON posts
    USING (user_id = current_user_id());  -- custom function or app.user_id setting

-- Using app.user_id (common pattern with connection pooler)
CREATE POLICY user_posts_policy ON posts
    USING (user_id = current_setting('app.user_id')::BIGINT);

-- Set at query time
SET app.user_id = 42;
SELECT * FROM posts;  -- only returns user 42's posts

Backup and Restore

-- PostgreSQL uses pg_dump and pg_restore

# Dump entire database (plain SQL)
pg_dump -U postgres -d myapp > backup.sql

# Dump in custom format (compressed, faster restore)
pg_dump -U postgres -d myapp -Fc > backup.dump

# Dump specific table
pg_dump -U postgres -d myapp -t users > users_backup.sql

# Restore from SQL
psql -U postgres -d myapp < backup.sql

# Restore from custom format
pg_restore -U postgres -d myapp backup.dump

# Restore single table
pg_restore -U postgres -d myapp -t users backup.dump

# Remote backup
pg_dump -h hostname -p 5432 -U postgres -d myapp -Fc > backup.dump

# pg_dumpall (all databases + roles)
pg_dumpall -U postgres > full_backup.sql

3 Real-World Projects

Project 1: Blog Database

CREATE TABLE authors (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        TEXT NOT NULL,
    email       TEXT NOT NULL UNIQUE,
    bio         TEXT,
    avatar_url  TEXT,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE categories (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        TEXT NOT NULL UNIQUE,
    slug        TEXT NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE articles (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    author_id    BIGINT NOT NULL REFERENCES authors(id),
    category_id  BIGINT REFERENCES categories(id),
    title        TEXT NOT NULL,
    slug         TEXT NOT NULL UNIQUE,
    excerpt      TEXT,
    body         TEXT NOT NULL,
    status       TEXT DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
    view_count   INTEGER DEFAULT 0,
    published_at TIMESTAMPTZ,
    created_at   TIMESTAMPTZ DEFAULT NOW(),
    updated_at   TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tags (
    id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    slug TEXT NOT NULL UNIQUE
);

CREATE TABLE article_tags (
    article_id BIGINT REFERENCES articles(id) ON DELETE CASCADE,
    tag_id     BIGINT REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (article_id, tag_id)
);

-- Indexes
CREATE INDEX idx_articles_author ON articles(author_id);
CREATE INDEX idx_articles_category ON articles(category_id);
CREATE INDEX idx_articles_status_published ON articles(published_at) WHERE status = 'published';

-- Popular articles query
SELECT
    a.title,
    a.view_count,
    au.name AS author,
    c.name AS category,
    array_agg(t.name) AS tags
FROM articles a
JOIN authors au ON au.id = a.author_id
LEFT JOIN categories c ON c.id = a.category_id
LEFT JOIN article_tags at ON at.article_id = a.id
LEFT JOIN tags t ON t.id = at.tag_id
WHERE a.status = 'published'
GROUP BY a.id, a.title, a.view_count, au.name, c.name
ORDER BY a.view_count DESC
LIMIT 10;

Project 2: E-Commerce Analytics

CREATE TABLE customers (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      TEXT NOT NULL UNIQUE,
    name       TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE products (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        TEXT NOT NULL,
    category    TEXT NOT NULL,
    price       NUMERIC(10, 2) NOT NULL,
    cost        NUMERIC(10, 2) NOT NULL
);

CREATE TABLE orders (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id),
    total       NUMERIC(10, 2) NOT NULL,
    status      TEXT DEFAULT 'pending',
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE order_items (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id    BIGINT NOT NULL REFERENCES orders(id),
    product_id  BIGINT NOT NULL REFERENCES products(id),
    quantity    INTEGER NOT NULL,
    unit_price  NUMERIC(10, 2) NOT NULL
);

-- Monthly revenue by category
SELECT
    DATE_TRUNC('month', o.created_at) AS month,
    p.category,
    SUM(oi.quantity * oi.unit_price) AS revenue,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(oi.quantity) AS units_sold
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'completed'
GROUP BY DATE_TRUNC('month', o.created_at), p.category
ORDER BY month DESC, revenue DESC;

-- Customer Lifetime Value (LTV)
SELECT
    c.name,
    c.email,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(o.total) AS total_spent,
    AVG(o.total) AS avg_order_value,
    MAX(o.created_at) AS last_order,
    EXTRACT(DAY FROM NOW() - MIN(o.created_at)) AS days_as_customer
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'completed'
GROUP BY c.id, c.name, c.email
ORDER BY total_spent DESC;

-- Best-selling products (by revenue)
SELECT
    p.name,
    p.category,
    SUM(oi.quantity) AS units_sold,
    SUM(oi.quantity * oi.unit_price) AS revenue,
    SUM(oi.quantity * (oi.unit_price - p.cost)) AS profit
FROM order_items oi
JOIN products p ON p.id = oi.product_id
JOIN orders o ON o.id = oi.order_id
WHERE o.status = 'completed'
GROUP BY p.id, p.name, p.category
ORDER BY revenue DESC
LIMIT 10;

Project 3: User Activity Tracking

CREATE TABLE events (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id     BIGINT REFERENCES users(id),
    session_id  UUID NOT NULL,
    event_type  TEXT NOT NULL,  -- 'page_view', 'click', 'purchase', etc.
    properties  JSONB DEFAULT '{}',
    ip_address  INET,
    user_agent  TEXT,
    created_at  TIMESTAMPTZ DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE events_2025_01 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE events_2025_02 PARTITION OF events
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- GIN index on JSONB for property filtering
CREATE INDEX idx_events_properties ON events USING GIN(properties);
CREATE INDEX idx_events_user_created ON events(user_id, created_at);

-- Daily active users
SELECT
    created_at::DATE AS date,
    COUNT(DISTINCT user_id) AS dau
FROM events
WHERE created_at >= NOW() - INTERVAL '30 days'
  AND user_id IS NOT NULL
GROUP BY created_at::DATE
ORDER BY date;

-- Funnel analysis: page_view → add_to_cart → purchase
WITH funnel AS (
    SELECT
        user_id,
        MAX(CASE WHEN event_type = 'page_view' THEN 1 ELSE 0 END) AS viewed,
        MAX(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) AS added,
        MAX(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) AS purchased
    FROM events
    WHERE created_at >= NOW() - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT
    SUM(viewed) AS total_viewers,
    SUM(added) AS added_to_cart,
    SUM(purchased) AS purchased,
    ROUND(100.0 * SUM(added) / NULLIF(SUM(viewed), 0), 1) AS view_to_cart_pct,
    ROUND(100.0 * SUM(purchased) / NULLIF(SUM(added), 0), 1) AS cart_to_purchase_pct
FROM funnel;

PostgreSQL vs MySQL vs SQLite vs SQL Server

Quick Reference

-- Database operations
CREATE DATABASE db;           DROP DATABASE db;
\l                            -- list databases
\c dbname                     -- connect

-- Table operations
CREATE TABLE t (...);         DROP TABLE t;
ALTER TABLE t ADD COLUMN c TEXT;
ALTER TABLE t DROP COLUMN c;
ALTER TABLE t RENAME COLUMN a TO b;
ALTER TABLE t ALTER COLUMN c SET NOT NULL;

-- CRUD
INSERT INTO t (a, b) VALUES (1, 2) RETURNING *;
SELECT * FROM t WHERE condition;
UPDATE t SET a = 1 WHERE condition RETURNING *;
DELETE FROM t WHERE condition RETURNING *;

-- Useful shortcuts
EXPLAIN ANALYZE SELECT ...;   -- query performance
\d tablename                  -- describe table
\dt                           -- list tables
SELECT * FROM pg_indexes WHERE tablename = 'users';
SELECT pg_size_pretty(pg_database_size('mydb'));  -- database size
SELECT pg_size_pretty(pg_total_relation_size('users'));  -- table size

-- Kill slow queries
SELECT pid, query, state FROM pg_stat_activity;
SELECT pg_terminate_backend(pid);

-- Useful views
SELECT * FROM pg_stat_user_tables;  -- table statistics
SELECT * FROM pg_stat_user_indexes;  -- index usage

Common Mistakes

PostgreSQL vs Related Terms

Learning Path

Free resources:

• postgresqltutorial.com
• postgresql.org/docs/current (official docs — excellent)
• pgexercises.com (interactive exercises)
• explain.dalibo.com (EXPLAIN plan visualizer)

6 FAQ

Q: Should I use PostgreSQL or MySQL?

For new projects: PostgreSQL. It has better JSON support, arrays, window functions, and stricter SQL compliance. MySQL's main advantage is slightly simpler setup and WordPress compatibility.

Q: What's the difference between PostgreSQL and Postgres?

They're the same database. "Postgres" is just the common nickname for PostgreSQL. The project is officially called PostgreSQL.

Q: How do I connect a Node.js app to PostgreSQL?

Use the pg package (node-postgres) or an ORM like Prisma or Drizzle:

npm install pg

const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const result = await pool.query('SELECT * FROM users WHERE id = $1', [userId]);

Q: How do I run PostgreSQL in production?

Options: managed services (Supabase, AWS RDS, Google Cloud SQL, Neon, Railway) or self-hosted with Docker. Use a connection pooler (PgBouncer) for high-traffic apps.

Q: What is VACUUM in PostgreSQL?

PostgreSQL's MVCC leaves old row versions behind when you UPDATE or DELETE. VACUUM reclaims this space. autovacuum runs automatically in the background — usually you don't need to run VACUUM manually.

Q: How do I handle migrations?

Use a migration tool: Flyway (Java ecosystem), Liquibase (any), Alembic (Python/SQLAlchemy), Prisma Migrate (Node.js), golang-migrate (Go), or Knex (Node.js).

PostgreSQL is more than a database — it's a platform. Start with CRUD, master JOINs and indexes, then explore JSON, window functions, and full-text search as your apps grow. The official documentation is exceptional and covers everything you'll ever need.