PostgreSQL: Types, JSONB and Advanced Writes

PostgreSQL is the most widely used open source relational database for new web applications and data platforms. It follows the SQL standard well, offers rich types and a high-performance optimizer. This course is based on PostgreSQL 18 (released in late 2025); everything presented also works on recent earlier versions, unless stated otherwise.

Precise types

CREATE TABLE commandes (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    reference   uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    client_id   bigint NOT NULL REFERENCES clients(id),
    montant     numeric(10,2) NOT NULL CHECK (montant >= 0),
    statut      text NOT NULL DEFAULT 'en_attente',
    options     jsonb NOT NULL DEFAULT '{}',
    cree_le     timestamptz NOT NULL DEFAULT now()
);

GENERATED ALWAYS AS IDENTITY is the standard form of auto-increment; serial is still accepted but no longer recommended.
uuid gives unguessable identifiers, useful in an API’s URLs. gen_random_uuid() produces v4 UUIDs; PostgreSQL 18 adds uuidv7(), whose values grow over time and index better.
numeric is exact: essential for money.
text has no performance penalty compared with varchar(n); add a CHECK if a maximum length is a real business rule.
timestamptz records an instant (converted to UTC) and displays it in the session’s time zone (SET TIME ZONE 'Europe/Paris'). The timestamp type without a time zone is ambiguous as soon as several time zones are involved.

JSONB: flexibility inside a relational model

jsonb stores JSON in a binary, indexable form. It is ideal for variable attributes (product characteristics, user preferences), not for replacing columns that are queried constantly.

SELECT nom,
       attributs -> 'taille'   AS taille_json,   -- jsonb result: "M"
       attributs ->> 'couleur' AS couleur        -- text result: noir
FROM produits
WHERE attributs @> '{"couleur": "noir"}';        -- contains this pair

->> returns text, to be used for comparing or displaying; @> tests containment and can use a GIN index (lesson 4). SQLite also offers -> and ->> (since version 3.38), which lets you practice in the browser.

Upsert and RETURNING

Inserting a row, or updating it if it already exists, is done in a single atomic statement:

INSERT INTO stocks (produit_id, quantite)
VALUES (42, 10)
ON CONFLICT (produit_id)                       -- requires a UNIQUE constraint or PK
DO UPDATE SET quantite = stocks.quantite + EXCLUDED.quantite
RETURNING produit_id, quantite;

EXCLUDED refers to the row you were trying to insert. ON CONFLICT DO NOTHING simply ignores duplicates. RETURNING returns the written rows (with their generated values: id, cree_le), which avoids an extra SELECT after every INSERT. For more complex synchronizations, PostgreSQL also has MERGE (since version 15).

First steps in psql

psql is the official command-line client:

psql -h localhost -U app -d boutique   -- connect
\dt                -- list tables
\d commandes       -- structure, indexes and constraints of a table
\x                 -- expanded display (one column per line)
\timing            -- show the duration of each query
\q                 -- quit

\d and \timing will serve you throughout the rest of the course to check your indexes and measure your optimizations.

Exercises

Solutions and tests stay on the server. In the student space, your code is run and checked, and the tutor gives hints before the solution.

CodeDifficulty 1SQL (SQLite) in the browser1 hint

Filter on a JSON attribute

Available table: produits(id, nom, attributs) where attributs contains a JSON object.

Display the name of the products whose couleur attribute is noir, using the ->> operator.

This exercise runs in SQLite, whose syntax is identical to PostgreSQL here.

Exercise data (SQL)

-- En PostgreSQL, attributs serait de type jsonb ; SQLite stocke le JSON en texte
CREATE TABLE produits (
  id INTEGER PRIMARY KEY,
  nom TEXT NOT NULL,
  attributs TEXT NOT NULL
);
INSERT INTO produits VALUES
 (1, 'T-shirt', '{"couleur": "noir", "taille": "M"}'),
 (2, 'Sweat', '{"couleur": "gris", "taille": "L"}'),
 (3, 'Casquette', '{"couleur": "noir"}'),
 (4, 'Chaussettes', '{"taille": "S", "lot": 3}');

SELECT nom
FROM produits
WHERE attributs ->> ... = ...;

CodeDifficulty 2SQL (SQLite) in the browser2 hints

Breakdown by color

Available table: produits(id, nom, attributs).

Display each color (alias couleur, with 'non renseignée' when the attribute is missing) and the number of products (alias nb).

This exercise runs in SQLite, whose syntax is identical to PostgreSQL here.

Exercise data (SQL)

-- En PostgreSQL, attributs serait de type jsonb ; SQLite stocke le JSON en texte
CREATE TABLE produits (
  id INTEGER PRIMARY KEY,
  nom TEXT NOT NULL,
  attributs TEXT NOT NULL
);
INSERT INTO produits VALUES
 (1, 'T-shirt', '{"couleur": "noir", "taille": "M"}'),
 (2, 'Sweat', '{"couleur": "gris", "taille": "L"}'),
 (3, 'Casquette', '{"couleur": "noir"}'),
 (4, 'Chaussettes', '{"taille": "S", "lot": 3}');

SELECT COALESCE(..., 'non renseignée') AS couleur, COUNT(*) AS nb
FROM produits
GROUP BY ...;

Multiple choiceDifficulty 11 hint
Timestamping events
An application records the login time of users located in France and Quebec. Which type should you choose?
- timestamp
- timestamptz
- text in ISO format
TaskDifficulty 21 hint
Stock upsert in psql
In a test PostgreSQL database, create a table stocks(produit_id bigint PRIMARY KEY, quantite integer NOT NULL CHECK (quantite >= 0)). Write a single statement that adds 10 units to product 42, creating the row if it does not exist, and that returns the final quantity. Run it twice.
- The table is created with a primary key on produit_id
- The statement uses INSERT ... ON CONFLICT (produit_id) DO UPDATE
- The update uses EXCLUDED.quantite to add up
- RETURNING returns produit_id and quantite
- The first run returns 10, the second 20

End-of-lesson quiz

What does attributs ->> ‘taille’ return on a jsonb column?
- A jsonb value
- A text value
- A boolean
What condition is required for INSERT … ON CONFLICT (email)?
- A GIN index on email
- A UNIQUE constraint (or primary key) on email
- A uuid column
What is RETURNING for?
- To undo a write
- To return the written rows, with their generated values
- To rerun the query on error

The rest of the course

Reading an Execution Plan with EXPLAIN ANALYZE20 min
B-tree Indexes: Creating, Composing, Making Them Usable20 min
Specialized Indexes: Partial, Expression, GIN, BRIN18 min
Optimizing Queries and Maintaining the Database20 min

Build my path See the full syllabus

PostgreSQL in Practice (indexes, performance) · Advanced · 3 h 36 min

Objectives