JSON
Aggregating Rows Into JSON With jsonb_agg in PostgreSQL
Build nested JSON documents from relational tables with jsonb_agg, jsonb_build_object and to_jsonb, avoid the join fan-out bug, and see which form is fastest.
8 min read
Library
Everything you need to level up your coding skills.
JSON
Using JSON_TABLE in PostgreSQL 17 to Turn JSON Into Rows
JSON_TABLE turns nested JSON into typed rows in one clause. Paths, nested arrays, ordinality, EXISTS columns and ON ERROR rules, run on PostgreSQL 18.
8 min read
JSON
Expanding JSONB Into Typed Rows With jsonb_to_recordset
Turn a JSONB array of objects into typed SQL columns with jsonb_to_recordset, jsonb_populate_recordset and jsonb_each, with the errors and edge cases.
7 min read
PostgreSQL
Typo Tolerant Search With pg_trgm in PostgreSQL Queries
Build typo tolerant search with pg_trgm in PostgreSQL: trigrams, similarity thresholds, GIN and GiST indexes, word similarity and full text fallback.
7 min read
JSON
Querying Arrays Inside JSONB With jsonb_array_elements
Search, count, filter and rebuild JSON arrays inside PostgreSQL JSONB, from containment tests to jsonb_array_elements, with timings for five tag lookups.
8 min read
JSON
Indexing JSONB With GIN in PostgreSQL and When It Helps
Build GIN indexes on JSONB, choose between jsonb_ops and jsonb_path_ops, and see the real size, build, insert and query costs on a 200,000-row table.
8 min read
JSON
Adding and Removing Keys From JSONB With jsonb_set and Minus
Add, rename, merge and delete keys and array elements in PostgreSQL JSONB with jsonb_set, jsonb_insert, || and minus, and see where each does nothing.
8 min read
JSON
Updating One Key Inside a JSONB Column Without Losing Data
Change a single field in a PostgreSQL JSONB document with jsonb_set or subscripts, increment a counter, and avoid the lost-update and rewrite costs.
8 min read
CSV
Loading a CSV From a URL Into PostgreSQL Without Saving It
Stream a CSV from a web address straight into COPY with curl. See what a 404 and a cut off download do to a plain pipe, and the guards that fix it.
8 min read
JSON
Extracting Nested JSONB Values With Path Operators
PostgreSQL reads deep JSONB values with #> and #>>, and filters with jsonpath. How array paths, missing keys, strict mode and wildcards actually behave.
8 min read
CSV
Loading a Gzipped CSV Into PostgreSQL Without Unpacking It
Stream a gzipped CSV into PostgreSQL with gunzip and COPY, time it against unpacking first, and see what a truncated archive does to a pipe.
8 min read
CSV
Skipping Bad Rows During a PostgreSQL CSV Import Safely
PostgreSQL 17 can skip rows with bad values during COPY. See ON_ERROR, REJECT_LIMIT and verbose logging in action, and the errors they do not cover.
7 min read
Get new articles by email
We send one email when a new article is published. No spam.