Part IV — PostgreSQL and MySQL in Practice

Chapter 15. PostgreSQL: Database Development

This chapter is PostgreSQL with the gloves off. The standard SQL of Part III works identically on both platforms; what differs — and what makes a developer fluent rather than merely functional — is each engine's own layer: PostgreSQL's type system (arrays, ranges, jsonb), its sequences and identity machinery, its deferrable constraints, its operator and function dialect, and its five index families. Every example runs on the canonical university database, and each feature is marked standard or PostgreSQL so the portability ledger of Chapter 13 stays honest.

The throughline is a claim worth testing as you read: PostgreSQL is closer to "a research project that ships" than any other major database — its extensions are consistent, documented, and composable, which is why they keep getting adopted elsewhere.

After studying this chapter you will be able to:

• Navigate PostgreSQL's database → schema → object organization and search_path.
• Choose among PostgreSQL's standard and extended data types.
• Use SERIAL, identity columns, and sequences deliberately.
• Declare constraints with PostgreSQL extras: deferrable checks and NOT VALID.
• Apply PostgreSQL operators: ILIKE, regular expressions, DISTINCT ON, generate_series, string_agg.
• Do date arithmetic with AGE, date_trunc, to_char, and intervals; query jsonb.
• Manage views and materialized views (including CONCURRENT refresh).
• Generate values with sequences and generated columns.
• Select among B-tree, hash, GiST, SP-GiST, GIN, and BRIN indexes.
• Move data with COPY and \copy.

15.1 PostgreSQL database and schema organization

A PostgreSQL server (an installation, one data directory) hosts one or more databases — fully isolated from each other (no queries across databases; connect per database). Inside each database, objects live in schemas — namespaces. Fresh databases get public (your default) and the system schemas: pg_catalog (the catalog — Chapter 4), information_schema (the standard view of it). Our tables are really public.student, public.enrollment.

The name you type resolves through **search_path** — an ordered list of schemas, default "$user", public. That machinery powers two professional patterns: multi-tenant schemas (one schema per customer, same table names, one SET search_path per session instead of N databases) and extensions — installed features land in their own schema (postgis, pgcrypto) and become callable through the path. Inspect it with SHOW search_path;, add yours with SET search_path = university_app, public;. Databases organize deployment, schemas organize naming — the fact MySQL conflates the two (Chapter 17) is the platforms' most structural vocabulary difference.

15.2 PostgreSQL data types

The standard families of Chapter 9, plus PostgreSQL's additions:

The natives, in one line each: arrays hold ordered lists in a cell (VARCHAR(9)[] of meeting days) — convenient, but Chapter 7's bridge-table rule still applies once you need to query into the list; **jsonb is the binary, indexable JSON type (Section 15.6); ranges** store intervals as values (tstzrange for a section's meeting window) with operators like && (overlaps); enums are named value lists (CREATE TYPE semester AS ENUM (...)) — domain constraints as types; domains are named constraint packages over a base type (CREATE DOMAIN gpa_type AS numeric(3,2) CHECK (VALUE BETWEEN 0 AND 4) — the Chapter 2 "domain" idea, literally implementable); composite types package columns as a reusable type. PostgreSQL's rule of thumb: if the concept has a type, use the type — types are constraints and documentation at once.

15.3 SERIAL and identity columns

Chapter 9 introduced the three spellings; PostgreSQL's details are the mastery here. SERIAL is not a type — it is shorthand: create a sequence, make the column integer NOT NULL DEFAULT nextval('seq'), and (usually) attach it to the primary key. The modern, standard spelling is the identity column:

CREATE TABLE applicant (
    applicant_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name    varchar(60) NOT NULL
);

INSERT INTO applicant (full_name) VALUES ('Dilruba Karim') RETURNING applicant_id;

 applicant_id
--------------
            1

GENERATED ALWAYS rejects manual key inserts (migrations use OVERRIDING SYSTEM VALUE); BY DEFAULT permits them. Identity columns are cleaner than SERIAL for three reasons: sequences they own are tied to the column (dropping the column drops the sequence — SERIAL leaks one), ALTER TABLE ... ALTER COLUMN ... RESTART WITH manages them uniformly, and they are standard SQL. Use identity for new designs, recognize SERIAL in every legacy schema, and reach for a bare CREATE SEQUENCE only when one generator feeds multiple tables or application logic — the next section's machinery.

15.4 Primary keys, foreign keys, and constraints

Chapter 9's constraint grammar, plus the two PostgreSQL extras that matter in production.

Deferrable constraints. PostgreSQL checks uniqueness and foreign keys immediately by default, but a constraint declared DEFERRABLE can postpone its check to transaction commit:

ALTER TABLE enrollment
    ADD CONSTRAINT enrollment_section_fk
    FOREIGN KEY (section_id) REFERENCES course_section(section_id)
    DEFERRABLE INITIALLY DEFERRED;

The use case is choreography: swapping two sections' numbers, or deleting-and-reinserting a parent row, inside one transaction — intermediate states that would violate the constraint are fine, as long as the transaction ends legal. SET CONSTRAINTS ... DEFERRED toggles per-transaction. (MySQL has no deferrable constraints — checks are immediate; Chapter 17.)

NOT VALID + VALIDATE. Adding a foreign key to a huge live table normally takes a long lock while every row is checked. PostgreSQL's two-step route: ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID; (checks only new rows, instant) then, later, ALTER TABLE ... VALIDATE CONSTRAINT ...; (checks existing rows with a shareable lock). This pair is the standard online-migration idiom, and it generalizes the Chapter 9 principle: constraints without downtime.

15.5 PostgreSQL-specific operators and functions

The dialect layer that makes PostgreSQL code read like PostgreSQL:

SELECT full_name
FROM   student
WHERE  full_name ILIKE '%rahman%'      -- case-insensitive LIKE (PostgreSQL)
  OR   full_name ~ '^A'                -- POSIX regular expression (PostgreSQL)
ORDER  BY full_name;

     full_name
--------------------
 Arif Mahmud
 Farhana Rahman

• **~ / ~*** — POSIX regex match (case-sensitive / -insensitive); SIMILAR TO adds SQL-standard patterns between LIKE and regex. For portability, LOWER() LIKE remains the Chapter 10 recommendation; for native code, ~ is idiomatic.
• **:: casts** — student_id::text, '2026-10-09'::date — the shorthand met in Chapter 11.
• **DISTINCT ON** — the one-statement top-per-group (Chapter 13).
• **generate_series** — rows from arithmetic: SELECT generate_series(1, 5); — the standard way to build calendars, test data, and ad hoc sequences.
• **string_agg / array_agg** — aggregation into a string or array: string_agg(full_name, ', ' ORDER BY gpa DESC) builds a class list in one query (Laboratory 3).
• **FILTER (WHERE ...)** — per-aggregate conditions (Chapter 11).
• **unnest — array to rows; lateral — per-row subqueries (Chapter 13); RETURNING** — DML results as a query (Chapters 10).

The professional habit is the ledger: each of these is a dialect entry in the Chapter 13 table — use them freely in PostgreSQL-native code, never in portable SQL, and always with a comment when the query may outlive the platform choice.

15.6 String, date, time, and JSON operations

Strings extend Chapter 11's set with the regex operators above, plus btrim/ltrim/rtrim variants, split_part(s, ',', 2) (field extraction from delimited strings — the CSV-in-a-cell rescue), initcap('arif mahmud') → 'Arif Mahmud', and format('%s has %s credits', full_name, total_credits) — printf for SQL.

Dates — the canonical set, with this book's pinned date:

SELECT full_name,
       AGE(DATE '2026-10-09', hire_date)      AS tenure,
       date_trunc('year', hire_date)          AS hire_year,
       to_char(hire_date, 'YYYY-MM')          AS ym,
       hire_date + INTERVAL '6 months'        AS review
FROM   instructor
WHERE  instructor_id = 101;

  full_name  |       tenure        | hire_year  |   ym    |   review
-------------+---------------------+------------+---------+------------
 Ahmed Kabir | 8 years 8 mons 24 days | 2018-01-01 | 2018-01 | 2018-07-15

AGE yields an interval (years-months-days of service); date_trunc floors a timestamp to a unit (the canonical month-bucketing tool); to_char formats for humans; AT TIME ZONE converts between named zones — the reason timestamptz plus SET timezone replaces every home-grown "store local time" scheme.

JSON. PostgreSQL has two JSON types: json (stored verbatim) and **jsonb** — parsed into a binary form that is slower to write, fast to query, and indexable. Use jsonb. The operator set, on a dev-table example:

CREATE TABLE course_catalog (
    dept_id   integer PRIMARY KEY REFERENCES department(dept_id),
    offering  jsonb NOT NULL
);
-- offering = {"courses": ["CSE221", "CSE251"], "accredited": true}

SELECT offering -> 'courses' -> 1        AS second_course,   -- jsonb: "CSE251"
       offering ->> 'accredited'         AS accredited,      -- text: true
       offering @> '{"courses": ["CSE221"]}' AS has_221      -- containment
FROM   course_catalog
WHERE  dept_id = 1;

-> returns jsonb, ->> returns text, @> is containment (the GIN-indexable workhorse), ? asks key existence, and jsonb_array_elements explodes arrays into rows — the bridge from semi-structured payloads to relational reporting. Chapter 27 returns to JSON as a modeling question; here it is a tool: log rows, API payloads, and flexible attributes that are read but not joined.

15.7 Views and materialized views

Chapter 13 covered the semantics; PostgreSQL's specifics are three. First, views are protected structures: dropping a base table is refused while a view depends on it (DROP ... CASCADE exists and is usually a mistake — Chapter 9). Second, **REFRESH MATERIALIZED VIEW CONCURRENTLY** refreshes without locking readers out — it requires a unique index on the matview, and it is the difference between "refresh at 2 a.m." and "refresh whenever" on a live reporting matview. Third, PostgreSQL materialized views are plain tables underneath: they can carry indexes and constraints, be backed up like tables, and be analyzed by Chapter 19's tooling — which is also their limit (no incremental refresh; a refresh recomputes the whole result unless CONCURRENTLY's diff logic applies).

15.8 Sequences and generated values

Sequences are PostgreSQL's generator primitive — identity columns wrap them (Section 15.3). Direct use:

CREATE SEQUENCE applicant_seq START 100;

SELECT nextval('applicant_seq');   -- 100
SELECT nextval('applicant_seq');   -- 101
SELECT currval('applicant_seq');   -- 101 (this session's last value)
SELECT setval('applicant_seq', 500);  -- jump the counter (data migration)

nextval never repeats values and never rolls back — a rolled-back transaction consumes its numbers, so gaps are normal and expected (a gapless requirement means sequences are the wrong tool). Sequences are shared objects: multiple tables may draw from one (a shared document-numbering stream across order, invoice, and credit-note tables). The second generator is the generated column: full_name_search text GENERATED ALWAYS AS (LOWER(full_name)) STORED — a computed, physically stored column (expression indexes, next section, often serve better since they stay in sync automatically).

15.9 PostgreSQL-specific indexing features

Chapter 19 is the full treatment; this section is the map. An index declaration chooses an access method:

Plus the three modifiers that matter most in practice: partial indexes (CREATE INDEX ... ON enrollment (student_id) WHERE grade IS NULL; — index only the in-progress rows, small and hot), expression indexes (CREATE INDEX ON student (LOWER(full_name)); — makes LOWER(full_name) = ... indexable, the portable-search fix), and covering indexes with INCLUDE (add columns so lookups skip the heap — Chapter 19 measures the difference). The catalog of methods is exactly what "extensible" meant in Chapter 1: each is a plug-in family the community extends.

15.10 Importing and exporting data

PostgreSQL's bulk-movement tool is COPY — the fastest row mover in the product (it bypasses per-statement overhead but not constraints):

COPY department FROM '/tmp/departments.csv' WITH (FORMAT csv, HEADER true);
COPY (SELECT student_id, full_name, gpa FROM student)
    TO '/tmp/students.csv' WITH (FORMAT csv, HEADER true);

Server-side COPY ... FROM '/path' runs as the server's OS user (path must be server-visible — the classic stumbling block), while **\copy** in psql runs client-side — same syntax, your machine's files, your permissions:

university=> \copy student TO 'students_backup.csv' WITH (FORMAT csv, HEADER true)
COPY 12

For full databases, pg_dump/pg_restore (Chapter 21) wrap COPY into portable archives. For JSON, the jsonb route of Section 15.6 plus COPY from CSV covers most ETL inputs; heavier pipelines graduate to Chapter 25. The counts discipline never changes: after any load, verify (5, 6, 12, 10, 13, 28 for the canonical files).

15.11 Practical PostgreSQL exercises

Six exercises, each answerable from this chapter and verifiable on the canonical database.

1 — Top per group, natively. Each department's top student via DISTINCT ON, with NULLs handled explicitly.

SELECT DISTINCT ON (major_dept_id) major_dept_id, full_name, gpa
FROM   student
WHERE  gpa IS NOT NULL
ORDER  BY major_dept_id, gpa DESC;

 major_dept_id |   full_name   | gpa
---------------+---------------+------
             1 | Arif Mahmud   | 3.90
             2 | Sadia Afrin   | 3.88
             3 | Imran Hossain | 3.15
             4 | Farhan Akter  | 2.98
             5 | Sumaiya Tabassum | 3.60

2 — A class list in one cell. string_agg the CSE students ordered by GPA.

SELECT string_agg(full_name, ', ' ORDER BY gpa DESC) AS honor_line
FROM   student WHERE major_dept_id = 1 AND gpa IS NOT NULL;

                          honor_line
---------------------------------------------------------------
 Arif Mahmud, Nusrat Jahan, Tanvir Alam, Rakib Hasan

3 — Tenure report. Each instructor's AGE-based tenure and next review date (the Section 15.6 pattern for all six).

4 — Deferrable swap. Inside one transaction, give section 12 a temporary duplicate instructor assignment that violates a UNIQUE constraint at statement time but is legal at commit (or demonstrate the reverse: a state illegal mid-transaction, legal at COMMIT). Capture the error when the constraint is not deferrable.

5 — JSON catalog. Build course_catalog with jsonb for all five departments; query with @> for every department offering CSE221; explode one offering with jsonb_array_elements.

6 — COPY round-trip. \copy student out to CSV, create student_copy (same columns), \copy back in, and verify COUNT(*) = 12 and a checksum-style comparison (e.g., EXCEPT in both directions returns no rows).

Chapter Summary

• Databases are isolated; schemas are namespaces resolved via search_path — the machinery for multi-tenancy and extensions; MySQL's database = schema is the structural divergence.
• Types: standard families plus arrays, jsonb, ranges, enums, domains, composites — "if the concept has a type, use the type."
• Identity columns are the modern, standard generator; SERIAL is legacy shorthand; bare sequences serve shared streams; gaps are normal.
• Deferrable constraints postpone checks to COMMIT (choreography); NOT VALID + VALIDATE adds constraints without downtime.
• Dialect operators: ILIKE, regex ~, ::, DISTINCT ON, generate_series, string_agg/array_agg, FILTER, unnest, RETURNING — each a Chapter 13 ledger entry.
• Dates: AGE, date_trunc, to_char, AT TIME ZONE, intervals; timestamptz is the clock type. jsonb: -> / ->> / @> / ?, GIN-indexable containment.
• Matviews refresh CONCURRENTLY with a unique index; they are plain tables underneath.
• Index families: btree, hash, GIN, GiST, SP-GiST, BRIN; plus partial, expression, and INCLUDE-covering forms.
• COPY (server) and \copy (client) move bulk data fastest; verify counts after every load.
• The six practical exercises rehearse the whole chapter against canonical data.

Key Terms

Laboratory Exercises

1. Schemas in action: create schema archive in university_dev, move nothing yet, but set search_path so an unqualified student resolves to archive.student — prove it with a CREATE TABLE that lands there, then reset. *Expected result: \dt archive.* shows the new table; SHOW search_path confirms the order; reset returns resolution to public.*
2. Types: build a meeting table using an enum type for day-of-week, a tstzrange for the meeting window, and a domain grade_domain re-usable on any grade column; insert three rows, one violating the domain. Expected result: two inserts succeed, one rejected by the domain's CHECK.
3. Identity vs sequence: create applicant with an identity column and insert three rows; create document_seq and draw two nextvals; then demonstrate a gap by consuming a value inside a rolled-back transaction. Expected result: identity 1–3; sequence 100, 101; after rollback the next nextval skips one number.
4. Deferrable choreography: with the Section 15.4 constraint, run the delete-and-reinsert of a student row in one transaction (cascaded enrollments make this safer in university_dev); repeat with the constraint's non-deferrable twin and capture the mid-transaction error. Expected result: deferred version commits cleanly; immediate version errors at the DELETE/INSERT that transiently violates the FK.
5. JSON catalog and queries: build course_catalog (Section 15.11 exercise 5) for all departments; query @> for CSE221; GIN-index the column and confirm the index is used (EXPLAIN preview is fine). Expected result: one department row (CSE); the plan shows a GIN index scan rather than a filter.
6. COPY round-trip: export student, rebuild student_copy, reimport, and verify equality both ways with EXCEPT. Expected result: 12 rows in, 12 rows out, zero rows in both EXCEPT directions.

Review Questions and Exercises

1. Why does an unqualified student in a fresh session resolve to public.student? search_path defaults to "$user", public — the user-schema (if any) then public.
2. When would you choose text over varchar(60), and what does varchar(60) still buy you? text when no length rule exists (PostgreSQL default); varchar(n) when the length is a domain rule you want enforced and documented.
3. What exactly does SERIAL expand to, and why prefer identity columns in new schemas? A sequence plus integer NOT NULL DEFAULT nextval; identity is standard, column-tied (no leaked sequence), and uniformly manageable.
4. A sequence shows a gap after a rolled-back insert. Is this a bug? Explain. No — nextval is non-transactional by design so concurrent generators stay fast; gaplessness and sequences are incompatible requirements.
5. What does DEFERRABLE INITIALLY DEFERRED change about a foreign key, and what does it never change? When the check runs (at COMMIT instead of per statement); what is checked — the end state must still be referentially legal.
6. Write the one-line fix that makes LOWER(full_name) = 'arif mahmud' indexable. *CREATE INDEX student_lower_name_idx ON student (LOWER(full_name));*
7. Contrast json and jsonb, and name the indexable containment operator. json stores text verbatim (preserving order/duplicates); jsonb parses to binary, faster to query, updatable — containment @> is GIN-indexable.
8. What do -> and ->> differ in, and which returns 'CSE251' as text from {"courses": ["CSE221", "CSE251"]}? *jsonb result vs text result; -> 'courses' ->> 1.*
9. Why does REFRESH ... CONCURRENTLY require a unique index on a materialized view? The concurrent refresh diffs old and new rows to update in place — it needs to identify rows uniquely to merge without blocking readers.
10. Match workload to access method: equality on a long token; jsonb payload fields; a 500-million-row append-only log by timestamp. hash; GIN; BRIN.
11. Why can server-side COPY fail with a permissions error while \copy of the same file succeeds? Server COPY reads the path as the server's OS user; \copy reads it as your client user — same syntax, different file-system identity.
12. Which three PostgreSQL features from this chapter would you refuse in a portable-SQL layer, and why? Any dialect items — ILIKE/regex, DISTINCT ON, ::casts, string_agg, FILTER, generate_series — they belong in the PostgreSQL-native layer with Chapter 13's ledger noting the portable alternative for each.

Mini-Project

Build the PostgreSQL workbook, pg_workbook.sql — ten numbered, comment-headed exercises that between them touch every section of this chapter, each with its expected output in a comment and a "standard or PostgreSQL?" tag: (1) a schema-based archive organization with search_path demonstration; (2) the typed meeting table (enum + range + domain); (3) identity-keyed applicant plus a shared document sequence feeding two tables; (4) the deferrable swap, both variants, with error captures; (5) the tenure report for all six instructors (AGE, date_trunc, to_char, intervals); (6) the jsonb course catalog with containment, key-existence, and array-explosion queries; (7) a materialized view of per-department GPA stats, refreshed CONCURRENTLY after a dev insert; (8) one index per access-method fit from your own workload guesses, each justified in a comment; (9) the COPY round-trip with EXCEPT verification; (10) a free-choice "one more feature" (lateral, unnest, FILTER — your pick) applied to the university data. Run it end to end in university_dev; the file is your PostgreSQL fluency receipt and Chapter 19's starting point.