Getting everything ready for you…

Loading courses, problems and your progress. This takes just a moment.

0s

PostgreSQL Documentation

The advanced open-source database — standard SQL, relationships, JSON, powerful indexes and queries.

PostgreSQL ("Postgres") is the most advanced open-source relational database. It follows the SQL standard closely and adds powerful features: JSONB documents, arrays, full-text search, window functions, CTEs, custom types and extensions like PostGIS. Startups and large companies alike choose it as their default database, and services like Supabase, Neon and Render offer managed Postgres.

1. Introduction & Installation

  • Windows/macOS: the EDB installer (includes pgAdmin), or Postgres.app on macOS.
  • Linux: sudo apt install postgresql, then sudo -u postgres psql.
  • Docker: docker run -e POSTGRES_PASSWORD=secret -p 5432:5432 postgres:16.
  • Cloud (free tiers): Supabase, Neon, Render.
psql --version
psql -h localhost -U postgres            # connect
createdb learn                           # create a database from the shell
psql -d learn -f schema.sql              # run a SQL file
pg_dump learn > backup.sql               # backup

Useful psql meta-commands: \l (databases), \c learn (connect), \dt (tables), \d users (describe), \x (expanded output), \timing, \q (quit).

⚠️ Common error: FATAL: password authentication failed for user "postgres" — use the password set during install, or check pg_hba.conf authentication settings.

2. Tables & Data Types

All examples use this sample learning-platform database:

-- Sample database: users, courses and enrollments
CREATE TABLE users (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       TEXT NOT NULL,
  email      TEXT NOT NULL UNIQUE,
  city       TEXT,
  joined_at  DATE NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE courses (
  id      SERIAL PRIMARY KEY,
  title   TEXT NOT NULL,
  level   TEXT NOT NULL CHECK (level IN ('beginner', 'intermediate', 'advanced')),
  price   NUMERIC(8, 2) NOT NULL DEFAULT 0,
  tags    TEXT[] NOT NULL DEFAULT '{}',
  meta    JSONB NOT NULL DEFAULT '{}'
);

CREATE TABLE enrollments (
  user_id     BIGINT REFERENCES users(id) ON DELETE CASCADE,
  course_id   INT REFERENCES courses(id),
  enrolled_on DATE NOT NULL,
  progress    INT NOT NULL DEFAULT 0 CHECK (progress BETWEEN 0 AND 100),
  PRIMARY KEY (user_id, course_id)
);

INSERT INTO users (name, email, city, joined_at) VALUES
  ('Asha',  'asha@example.com',  'Pune',   '2026-01-10'),
  ('Ravi',  'ravi@example.com',  'Delhi',  '2026-02-14'),
  ('Neha',  'neha@example.com',  'Pune',   '2026-03-01'),
  ('Kabir', 'kabir@example.com', 'Mumbai', '2026-05-20');

INSERT INTO courses (title, level, price, tags, meta) VALUES
  ('DSA in C++',      'intermediate', 0,      '{dsa,cpp}',      '{"hours": 40, "teacher": "Striver"}'),
  ('React Basics',    'beginner',     499.00, '{web,react,js}', '{"hours": 12, "teacher": "Asha"}'),
  ('System Design',   'advanced',     999.00, '{design}',       '{"hours": 20, "certificate": true}'),
  ('Python for Data', 'beginner',     0,      '{python,data}',  '{"hours": 18}');

INSERT INTO enrollments VALUES
  (1, 1, '2026-02-01', 80),
  (1, 2, '2026-03-05', 100),
  (2, 1, '2026-03-10', 35),
  (3, 2, '2026-04-01', 60),
  (3, 4, '2026-04-02', 10),
  (4, 3, '2026-06-01', 0);
SELECT id, title, level, price, tags FROM courses ORDER BY id;

Output:

id | title           | level        | price  | tags
1  | DSA in C++      | intermediate | 0.00   | {dsa,cpp}
2  | React Basics    | beginner     | 499.00 | {web,react,js}
3  | System Design   | advanced     | 999.00 | {design}
4  | Python for Data | beginner     | 0.00   | {python,data}
Type Use
INT, BIGINT, SERIAL / IDENTITY integers, auto-increment ids
NUMERIC(p, s) exact decimals (money)
REAL, DOUBLE PRECISION approximate numbers
TEXT, VARCHAR(n) strings (TEXT has no length limit and no penalty)
BOOLEAN true / false / NULL
DATE, TIMESTAMPTZ, INTERVAL dates, timestamps with time zone, durations
UUID universally unique ids (gen_random_uuid())
JSONB binary JSON with indexing
type[] arrays (TEXT[], INT[])

⚠️ Common error: using TIMESTAMP (without time zone) for real events. Use TIMESTAMPTZ — it stores an exact moment and converts to the client's time zone.

3. Querying Data

SELECT title, price
FROM courses
WHERE price > 0 OR level = 'beginner'
ORDER BY price DESC, title;

SELECT name, city FROM users WHERE name ILIKE 'a%' OR city ILIKE 'MUM%';   -- ILIKE = case-insensitive
title           | price
System Design   | 999.00
React Basics    | 499.00
Python for Data | 0.00

name  | city
Asha  | Pune
Kabir | Mumbai
SELECT DISTINCT ON (city) city, name, joined_at     -- first row per city (Postgres feature)
FROM users
ORDER BY city, joined_at;
city   | name  | joined_at
Delhi  | Ravi  | 2026-02-14
Mumbai | Kabir | 2026-05-20
Pune   | Asha  | 2026-01-10

⚠️ Common error: column "Name" does not exist — unquoted identifiers are lower-cased. Don't create mixed-case names with double quotes; stick to snake_case.

4. Functions & Operators

SELECT
  title,
  upper(left(title, 3))                     AS code,
  length(title)                             AS len,
  price * 1.18                              AS with_gst,
  CASE WHEN price = 0 THEN 'free' ELSE 'paid' END AS kind,
  title || ' (' || level || ')'             AS label
FROM courses
ORDER BY id;
title           | code | len | with_gst  | kind | label
DSA in C++      | DSA  | 10  | 0.0000    | free | DSA in C++ (intermediate)
React Basics    | REA  | 12  | 588.8200  | paid | React Basics (beginner)
System Design   | SYS  | 13  | 1178.8200 | paid | System Design (advanced)
Python for Data | PYT  | 15  | 0.0000    | free | Python for Data (beginner)

Dates and intervals:

SELECT name,
       joined_at,
       to_char(joined_at, 'DD Mon YYYY')       AS pretty,
       joined_at + INTERVAL '30 days'          AS trial_ends,
       DATE '2026-10-20' - joined_at           AS days_member,
       EXTRACT(MONTH FROM joined_at)           AS month
FROM users
ORDER BY joined_at
LIMIT 2;
name | joined_at  | pretty      | trial_ends          | days_member | month
Asha | 2026-01-10 | 10 Jan 2026 | 2026-02-09 00:00:00 | 283         | 1
Ravi | 2026-02-14 | 14 Feb 2026 | 2026-03-16 00:00:00 | 248         | 2

Others: now(), CURRENT_DATE, date_trunc('month', ts), age(a, b), coalesce, nullif, greatest/least, string_agg, split_part, regexp_replace, round(x::numeric, 2), casting with :: ('42'::int).

5. Aggregation & Grouping

SELECT level,
       count(*)                                  AS courses,
       round(avg(price), 2)                      AS avg_price,
       string_agg(title, ', ' ORDER BY title)    AS titles,
       count(*) FILTER (WHERE price = 0)         AS free_courses
FROM courses
GROUP BY level
ORDER BY level;
level        | courses | avg_price | titles                        | free_courses
advanced     | 1       | 999.00    | System Design                 | 0
beginner     | 2       | 249.50    | Python for Data, React Basics | 1
intermediate | 1       | 0.00      | DSA in C++                    | 1

FILTER (WHERE ...) lets one query compute several conditional aggregates. GROUP BY ROLLUP (a, b), CUBE and GROUPING SETS produce subtotals.

⚠️ Common error: avg() on an INT column returns many decimals (61.6666666666666667). Round it: round(avg(progress), 1).

6. Joins

SELECT u.name, c.title, e.progress
FROM enrollments e
JOIN users u   ON u.id = e.user_id
JOIN courses c ON c.id = e.course_id
WHERE e.progress >= 50
ORDER BY e.progress DESC, u.name;
name | title        | progress
Asha | React Basics | 100
Asha | DSA in C++   | 80
Neha | React Basics | 60
-- every course with its number of learners and average progress
SELECT c.title, count(e.user_id) AS learners, coalesce(round(avg(e.progress)), 0) AS avg_progress
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.id
GROUP BY c.id, c.title
ORDER BY learners DESC, c.title;
title           | learners | avg_progress
DSA in C++      | 2        | 58
React Basics    | 2        | 80
Python for Data | 1        | 10
System Design   | 1        | 0

PostgreSQL supports INNER, LEFT, RIGHT, FULL OUTER, CROSS and LATERAL joins (a subquery that can reference earlier tables — great for "top N per group").

7. Subqueries, CTEs & Window Functions

WITH per_user AS (
  SELECT user_id, count(*) AS courses, avg(progress) AS avg_progress
  FROM enrollments
  GROUP BY user_id
)
SELECT u.name, p.courses, round(p.avg_progress, 1) AS avg_progress,
       rank() OVER (ORDER BY p.avg_progress DESC) AS rank
FROM per_user p
JOIN users u ON u.id = p.user_id
ORDER BY rank, u.name;
name  | courses | avg_progress | rank
Asha  | 2       | 90.0         | 1
Neha  | 2       | 35.0         | 2
Ravi  | 1       | 35.0         | 2
Kabir | 1       | 0.0          | 4
-- running total of enrollments per month
SELECT to_char(date_trunc('month', enrolled_on), 'YYYY-MM') AS month,
       count(*) AS enrollments,
       sum(count(*)) OVER (ORDER BY date_trunc('month', enrolled_on)) AS running_total
FROM enrollments
GROUP BY date_trunc('month', enrolled_on)
ORDER BY month;
month   | enrollments | running_total
2026-02 | 1           | 1
2026-03 | 2           | 3
2026-04 | 2           | 5
2026-06 | 1           | 6

Recursive CTEs walk hierarchies (categories, org charts) or generate series:

WITH RECURSIVE countdown(n) AS (
  SELECT 3
  UNION ALL
  SELECT n - 1 FROM countdown WHERE n > 1
)
SELECT string_agg(n::text, ' ') AS numbers FROM countdown;
numbers
3 2 1

8. Modifying Data

INSERT INTO users (name, email, city) VALUES ('Isha', 'isha@example.com', 'Delhi')
RETURNING id, name;                                     -- get generated values back
id | name
5  | Isha
-- UPSERT: insert, or update progress if the enrollment already exists
INSERT INTO enrollments (user_id, course_id, enrolled_on, progress)
VALUES (2, 1, '2026-10-01', 50)
ON CONFLICT (user_id, course_id) DO UPDATE SET progress = GREATEST(enrollments.progress, EXCLUDED.progress);

UPDATE courses SET price = price * 0.9 WHERE level = 'beginner' AND price > 0
RETURNING title, price;          -- NUMERIC(8, 2) stores the value rounded to 2 decimals

DELETE FROM users WHERE email = 'kabir@example.com';    -- cascades to his enrollments

SELECT user_id, course_id, progress FROM enrollments WHERE user_id IN (2, 4) ORDER BY user_id;
title        | price
React Basics | 449.10

user_id | course_id | progress
2       | 1         | 50

Schema changes: ALTER TABLE users ADD COLUMN phone TEXT;, ALTER TABLE users ALTER COLUMN city SET NOT NULL;, ALTER TABLE ... RENAME COLUMN, ALTER TABLE ... ADD CONSTRAINT. Most DDL is transactional in Postgres — you can roll back a failed migration.

⚠️ Common error: duplicate key value violates unique constraint — handle it with ON CONFLICT, or check before inserting.

9. Indexes & Transactions

CREATE INDEX idx_enrollments_course ON enrollments (course_id);
CREATE INDEX idx_users_lower_email ON users (lower(email));            -- expression index
CREATE INDEX idx_courses_tags ON courses USING GIN (tags);             -- arrays / JSONB
CREATE INDEX idx_active ON enrollments (user_id) WHERE progress < 100; -- partial index
EXPLAIN ANALYZE SELECT * FROM users WHERE lower(email) = 'asha@example.com';

Index types: B-tree (default, equality and ranges), GIN (arrays, JSONB, full-text), GiST (geometry, ranges), BRIN (huge, naturally ordered tables), Hash.

BEGIN;
UPDATE enrollments SET progress = 100 WHERE user_id = 1 AND course_id = 1;
SAVEPOINT before_risky;
UPDATE courses SET price = -1 WHERE id = 1;   -- would violate nothing here, but imagine it's a mistake
ROLLBACK TO before_risky;
COMMIT;

SELECT progress FROM enrollments WHERE user_id = 1 AND course_id = 1;
SELECT price FROM courses WHERE id = 1;
progress
100

price
0.00

PostgreSQL uses MVCC — readers never block writers. Default isolation is READ COMMITTED; use REPEATABLE READ or SERIALIZABLE when needed, and SELECT ... FOR UPDATE to lock rows.

10. Advanced Concepts

JSONB — store flexible data and query it:

SELECT title,
       meta->>'teacher'          AS teacher,        -- ->> returns text
       (meta->>'hours')::int     AS hours,
       meta ? 'certificate'      AS has_cert        -- key exists?
FROM courses
WHERE (meta->>'hours')::int >= 15
ORDER BY hours DESC;

SELECT title FROM courses WHERE meta @> '{"teacher": "Striver"}';   -- containment (uses GIN index)
title           | teacher | hours | has_cert
DSA in C++      | Striver | 40    | f
System Design   | NULL    | 20    | t
Python for Data | NULL    | 18    | f

title
DSA in C++

Arrays:

SELECT title, array_length(tags, 1) AS n_tags, tags[1] AS first_tag
FROM courses
WHERE 'web' = ANY (tags) OR tags && ARRAY['data'];      -- && = overlaps

SELECT unnest(tags) AS tag, count(*) AS courses
FROM courses
GROUP BY tag
HAVING count(*) >= 1
ORDER BY tag
LIMIT 3;
title           | n_tags | first_tag
React Basics    | 3      | web
Python for Data | 2      | python

tag    | courses
cpp    | 1
data   | 1
design | 1

Full-text search:

SELECT title
FROM courses
WHERE to_tsvector('english', title) @@ to_tsquery('english', 'design | data')
ORDER BY title;
title
Python for Data
System Design

Views, functions and more:

CREATE VIEW course_stats AS
SELECT c.title, count(e.*) AS learners FROM courses c LEFT JOIN enrollments e ON e.course_id = c.id GROUP BY c.title;

CREATE FUNCTION completion_rate(cid INT) RETURNS NUMERIC
LANGUAGE sql AS $$
  SELECT round(100.0 * count(*) FILTER (WHERE progress = 100) / NULLIF(count(*), 0), 1)
  FROM enrollments WHERE course_id = cid
$$;

SELECT completion_rate(2) AS react_completion;
react_completion
50.0

Also explore: materialized views, triggers (PL/pgSQL), LISTEN/NOTIFY, roles and row-level security, extensions (pgcrypto, PostGIS, pg_stat_statements, pgvector for AI embeddings), connection pooling (PgBouncer), and replication.

From Node.js with pg:

import pg from "pg";
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
const { rows } = await pool.query("SELECT title FROM courses WHERE level = $1 AND price <= $2", ["beginner", 500]);
console.log(rows);

11. Examples with Output

Example 1 — Top course per level (DISTINCT ON)

SELECT DISTINCT ON (level) level, title, price
FROM courses
ORDER BY level, price DESC;
level        | title         | price
advanced     | System Design | 999.00
beginner     | React Basics  | 499.00
intermediate | DSA in C++    | 0.00

Example 2 — Learners who haven't started (progress = 0) or are stuck below 40%

SELECT u.name, c.title, e.progress,
       CASE WHEN e.progress = 0 THEN 'not started' ELSE 'needs a nudge' END AS status
FROM enrollments e
JOIN users u ON u.id = e.user_id
JOIN courses c ON c.id = e.course_id
WHERE e.progress < 40
ORDER BY e.progress;
name  | title           | progress | status
Kabir | System Design   | 0        | not started
Neha  | Python for Data | 10       | needs a nudge
Ravi  | DSA in C++      | 35       | needs a nudge

Example 3 — Generate a monthly calendar of sign-ups (including empty months)

SELECT to_char(m, 'YYYY-MM') AS month, count(u.id) AS signups
FROM generate_series(DATE '2026-01-01', DATE '2026-05-01', INTERVAL '1 month') AS m
LEFT JOIN users u ON date_trunc('month', u.joined_at) = m
GROUP BY m
ORDER BY m;
month   | signups
2026-01 | 1
2026-02 | 1
2026-03 | 1
2026-04 | 0
2026-05 | 1

12. Practice Problems

Use the sample database above:

  1. List beginner courses with their number of learners.
  2. Find users enrolled in more than one course, with their average progress.
  3. For each course, show the learner with the highest progress (DISTINCT ON or a window function).
  4. Find courses tagged web or react using array operators.
  5. Store a rating inside meta JSONB for one course and list courses sorted by rating.
  6. Write an UPSERT that only increases progress, never decreases it.
  7. Create a GIN index on meta and check it is used with EXPLAIN.
  8. Build a course_stats materialized view and refresh it.

13. Interview Questions

1. Why choose PostgreSQL over MySQL? Stronger SQL standard support, richer types (JSONB, arrays, ranges), advanced indexes (GIN, GiST), transactional DDL, powerful extensions and window/CTE support for a long time.

2. JSON vs JSONB? JSON stores the raw text; JSONB stores a parsed binary form — slightly slower to insert but much faster to query and indexable with GIN.

3. What is MVCC? Multi-Version Concurrency Control: each transaction sees a snapshot, updates create new row versions, so readers and writers don't block each other. VACUUM cleans old versions.

4. SERIAL vs IDENTITY? Both auto-increment. GENERATED ... AS IDENTITY is the SQL-standard, recommended form; SERIAL is older shorthand using a sequence.

5. What index types exist? B-tree (default), Hash, GIN (arrays/JSONB/full-text), GiST (geometric, ranges), SP-GiST, BRIN (very large ordered tables).

6. What does VACUUM do? Reclaims space from dead row versions and updates visibility info; autovacuum runs it automatically. ANALYZE updates statistics for the planner.

7. What is a CTE and when is it recursive? A named subquery defined with WITH. WITH RECURSIVE references itself to walk hierarchies or generate sequences.

8. EXPLAIN vs EXPLAIN ANALYZE? EXPLAIN shows the planned execution; EXPLAIN ANALYZE actually runs the query and reports real timings and row counts.

9. What is ON CONFLICT? Postgres's UPSERT: on a unique-constraint conflict, DO NOTHING or DO UPDATE SET ... using EXCLUDED values.

10. How do you avoid SQL injection in Postgres apps? Use parameterised queries ($1, $2 placeholders) — never build SQL strings from user input.

14. Cheat Sheet & References

psql

# connect and run files
psql -h localhost -U postgres -d mydb
psql -d mydb -f schema.sql
pg_dump mydb > backup.sql
psql -d mydb < backup.sql

Meta-commands

\l   \c dbname   \dt   \d table   \du   \x   \timing   \i file.sql   \q

Tables

CREATE TABLE t (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT NOT NULL, tags TEXT[] DEFAULT '{}', meta JSONB DEFAULT '{}', created_at TIMESTAMPTZ DEFAULT now());
ALTER TABLE t ADD COLUMN email TEXT UNIQUE; ALTER TABLE t ALTER COLUMN name SET NOT NULL;
CREATE INDEX ON t (lower(email)); CREATE INDEX ON t USING GIN (meta);

Queries

SELECT * FROM t WHERE name ILIKE 'a%' ORDER BY id DESC LIMIT 10 OFFSET 20;
SELECT DISTINCT ON (city) city, name FROM users ORDER BY city, joined_at;
SELECT level, count(*) FILTER (WHERE price = 0) FROM courses GROUP BY level;
SELECT string_agg(name, ', ' ORDER BY name) FROM users;
WITH x AS (SELECT ...) SELECT ...; WITH RECURSIVE r AS (...) SELECT ...;
SELECT rank() OVER (PARTITION BY a ORDER BY b DESC) FROM t;

Writes

INSERT INTO t (name) VALUES ('x') RETURNING id;
INSERT INTO t (id, n) VALUES (1, 1) ON CONFLICT (id) DO UPDATE SET n = EXCLUDED.n;
UPDATE t SET n = n + 1 WHERE id = 1 RETURNING *;
BEGIN; SAVEPOINT s; ROLLBACK TO s; COMMIT;

JSONB and arrays

meta->'a'; meta->>'a'; meta @> '{"a": 1}'; meta ? 'key'; jsonb_set(meta, '{a}', '2');
'x' = ANY (tags); tags && ARRAY['a','b']; array_append(tags, 'z'); unnest(tags); array_length(tags, 1);

Dates and casts

now(); CURRENT_DATE; date_trunc('month', ts); ts + INTERVAL '7 days'; age(a, b);
to_char(d, 'DD Mon YYYY'); EXTRACT(YEAR FROM d); '42'::int; x::numeric(10,2);
generate_series(1, 10); gen_random_uuid();

Official documentation