JSON
JSON_EXTRACT and the Arrow Operators in MySQL Explained
How MySQL JSON paths work, what JSON_EXTRACT returns for each value type, why -> and ->> are not the same, and the path rules that cause errors.
7 min read
Library
Everything you need to level up your coding skills.
CSV
How to Run SQL Directly on a CSV File With the DuckDB CLI
Install DuckDB, point it at a CSV and query it. Covers the file name shortcut, DESCRIBE, SUMMARIZE, reading from a pipe and writing CSV back out.
7 min read
YAML
Environment Specific YAML Config Without Copy and Paste
Keep one base file and small overrides per environment, merge them in order and print the effective config with the source of each value. Tested layers.
6 min read
Excel
Pivoting Rows Into Columns in MySQL and SQL Server
Pivot in MySQL with SUM and CASE, and in SQL Server with the PIVOT operator. Includes dynamic column lists built with prepared statements and QUOTENAME.
7 min read
XML
Analyzing Podcast and Blog Feeds From XML With Python
Turn an RSS podcast feed into cadence, duration and size numbers, handle itunes durations in two formats and RFC 822 dates, and compare with feedparser.
6 min read
Editor's pick
CSVGuide
Querying CSV Files With DuckDB Instead of Loading Them
DuckDB runs SQL on a CSV file with no import, no schema and no server. See what it guesses, what that costs, where it fails, and how to make it strict.
22 min read
PostgreSQL
Prefix Search and Autocomplete in PostgreSQL Without Pain
Build prefix search and autocomplete in PostgreSQL with text_pattern_ops, tsquery prefix matching, trigram indexes, ranking by popularity and debouncing.
6 min read
Excel
Pivoting Rows Into Columns in PostgreSQL With FILTER
Turn rows into columns in PostgreSQL with conditional aggregates and FILTER, or with crosstab from tablefunc, and generate the column list when it changes.
6 min read
JSON
Creating and Querying a MySQL JSON Column From Scratch
Create a MySQL JSON column with a default, insert documents several ways, then filter, sort and group on keys, with the NULL and ordering traps checked.
8 min read
CSV
Why SQLite Stores Your CSV Numbers as Text and How to Fix It
SQLite .import stores every CSV value as text, so ORDER BY puts 10.25 before 2.75 and amount > 9 matches every row. See the cause and three ways to fix it.
8 min read
XML
Reading Financial and Government XML Datasets With Python
Read the ECB daily reference rates XML: two namespaces, rates as attributes, 29 currencies, a 64-day history, and Decimal for the arithmetic.
8 min read
CSV
Turning a Folder of CSV Files Into a SQLite Database
One table per CSV file, with safe names and types inferred from the whole file. A short Python script that keeps ZIP codes as text and refuses ragged files.
9 min read
Get new articles by email
We send one email when a new article is published. No spam.