PostgreSQL Advanced Features

This lesson covers the PostgreSQL features you will use in real projects: JSONB, common table expressions, views, indexes and transactions.

JSONB

A JSONB column stores a JSON document. It is useful for extra details that differ from row to row, such as a student's profile settings.

Example

sql

ALTER TABLE students ADD COLUMN profile JSONB;

UPDATE students
SET profile = '{"phone": "0300-1112233", "laptop": true, "skills": ["HTML", "CSS"]}'
WHERE id = 1;

UPDATE students
SET profile = '{"phone": "0321-4445566", "laptop": false, "skills": ["Python"]}'
WHERE id = 2;

Reading JSONB

Operator	Meaning	Example
`->`	Get a JSON value	`profile -> 'skills'`
`->>`	Get a value as text	`profile ->> 'phone'`
`@>`	Contains	`profile @> '{"laptop": true}'`
`?`	Has this key	`profile ? 'phone'`

Example

sql

SELECT name, profile ->> 'phone' AS phone
FROM students
WHERE profile @> '{"laptop": true}';

Output

   name   |    phone
----------+--------------
 Ali Khan | 0300-1112233
(1 row)

A GIN index makes @> and ? searches fast on large tables:

Example

sql

CREATE INDEX idx_students_profile ON students USING GIN (profile);

JSONB is great for flexible extras, but keep important, always-present data (name, city, course) in normal columns. Normal columns are easier to check with constraints and to join.

Common Table Expressions (WITH)

A CTE gives a name to a sub-query, so a long query reads step by step:

Example

sql

WITH course_stats AS (
  SELECT course_id, ROUND(AVG(marks), 1) AS avg_marks
  FROM students
  WHERE course_id IS NOT NULL
  GROUP BY course_id
)
SELECT c.title, cs.avg_marks
FROM course_stats AS cs
JOIN courses AS c ON c.id = cs.course_id
ORDER BY cs.avg_marks DESC;

Output

       title        | avg_marks
--------------------+-----------
 Graphic Designing  |      92.0
 Python Programming |      86.5
 Web Development    |      71.0
(3 rows)

First course_stats works out the average per course; then the main query adds the course titles.

Views

A view saves a query under a name:

Example

sql

CREATE VIEW student_courses AS
SELECT s.id, s.name, s.city, c.title AS course
FROM students AS s
LEFT JOIN courses AS c ON c.id = s.course_id;

SELECT name, course FROM student_courses WHERE city = 'Lahore';

Output

      name       |       course
-----------------+--------------------
 Ayesha Siddiqui | Python Programming
 Sana Iqbal      | Python Programming
(2 rows)

PostgreSQL also has materialized views, which store the result like a table. They are fast to read, and you update them with REFRESH MATERIALIZED VIEW name;.

Indexes and EXPLAIN

Example

sql

CREATE INDEX idx_students_city ON students (city);

EXPLAIN ANALYZE runs a query and shows how PostgreSQL executed it:

Example

sql

EXPLAIN ANALYZE SELECT name FROM students WHERE city = 'Karachi';

Look for Index Scan (the index was used) or Seq Scan (the whole table was read). On a tiny table PostgreSQL may still choose Seq Scan, because reading six rows is faster than using an index. That is normal.

Index type	Good for
B-tree (default)	`=`, `<`, `>`, `BETWEEN`, `ORDER BY`
GIN	JSONB, arrays, full-text search
GiST	Maps and geometry (PostGIS)

Transactions

Wrap related changes in BEGIN and COMMIT, so they succeed or fail together. Moving Hamza into a course and recording it in a log table:

Example

sql

CREATE TABLE admissions_log (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  student_id INTEGER NOT NULL REFERENCES students (id),
  course_id INTEGER NOT NULL REFERENCES courses (id),
  note TEXT,
  logged_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Example

sql

BEGIN;

UPDATE students SET course_id = 4 WHERE id = 5;

INSERT INTO admissions_log (student_id, course_id, note)
VALUES (5, 4, 'Joined Digital Marketing');

COMMIT;

If any statement fails, PostgreSQL refuses the rest of the transaction until you type ROLLBACK;, and nothing is saved. You can also choose to cancel at any time with ROLLBACK;.

Unlike many databases, PostgreSQL can roll back table changes too. A CREATE TABLE or ALTER TABLE inside a transaction is undone by ROLLBACK, which makes trying out changes much safer.

Exercise

Which operator returns a JSONB value as plain text, for example the phone number?

->->>@>||