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
Tag
how-to
590 posts
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
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
YAML
Keeping Secrets Out of YAML Config Files and Git History
A password removed from a YAML file stayed in Git history. Reference secrets by name, scan the tree and the history, and rotate anything that was committed.
6 min read
Excel
Writing the Same Excel Pivot Table as a GROUP BY Query
Map each part of an Excel pivot table to SQL: rows to GROUP BY, values to aggregates, columns to CASE, filters to WHERE and totals to ROLLUP.
6 min read
CSV
Loading Many CSV Files Into One SQLite Database in Bash
A bash loop that imports monthly CSV files into one SQLite table, records which file each row came from, and refuses to load a file twice.
7 min read
JSONGuide
Using the JSON Column Type in MySQL Without Regret
A working guide to MySQL 8.4 JSON columns: paths, reading, updating, searching, generated-column and multi-valued indexes, and MariaDB differences.
19 min read
XML
Summarizing a Large XML Export With a Streaming Parser
Total amounts by currency and top SKUs over 20,000 invoices in one streaming pass with Decimal, 21 MB of memory, and the same USD total as a tree-based check.
6 min read
Excel
Grouping Dates by Month or Quarter in a Pivot Table
Group dates in a pivot table by month, quarter or year, fix the Cannot group that selection error, handle fiscal quarters, and do the same grouping in SQL.
6 min read
Excel
Making a Pivot Table From Data Larger Than Excel Allows
A sheet holds 1,048,576 rows, but a pivot can read more. Use the Data Model, pre-aggregate in SQL or DuckDB, or sample, and know where each approach stops.
6 min read