CSV
Querying a CSV File in SQLite Without Importing It
Compile SQLite's CSV virtual table, query a file in place, and compare the cost with importing it and with DuckDB, including the typed schema surprises.
6 min read
Library
Everything you need to level up your coding skills.
Excel
Building a Pivot Table From a Database Query in Excel
Connect Excel to a database, load a SQL query as a connection and build a pivot table on it that refreshes. Covers the Data Model, parameters and traps.
7 min read
XMLGuide
Analysis on XML Data When Nobody Has Converted It Yet
A method for questions about an XML file nobody has converted: look, profile, sample, pick a tool, answer, then prove the numbers with a second method.
17 min read
CSV
Importing a CSV Into an Existing SQLite Table Step by Step
Importing into a table that holds data goes wrong four ways: duplicate keys, missing columns, extra columns and a new order. See each and the staging fix.
7 min read
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
ExcelGuide
Pivot Tables and Their SQL Equivalents for Excel Users
For people who know Excel pivot tables and need the SQL behind them: GROUP BY, conditional aggregates, ROLLUP, window functions, unpivoting and checks.
19 min read
CSV
Setting Column Types When SQLite Imports a CSV File Safely
sqlite3 .import makes untyped columns. Create the table yourself, choose between INTEGER, TEXT and STRICT, and see what a ZIP code and a bad price do in each.
7 min read
YAMLGuide
Structuring YAML Configuration Files That Last for Years
How to lay out YAML config so it stays correct: base and overlays, secret references, a schema, strict loading, key style, comments and a CI check, tested.
17 min read
XML
Testing Feed Compatibility Before a Schema Change Ships
Which XSD edits break old documents, which break old consumers, and a script that tests six changes both ways against saved samples before you ship.
6 min read
Excel
Why SQL Beats Formulas Once the Data Gets Large and Slow
Why spreadsheet formulas slow down as data grows and where SQL pulls ahead: lookup cost, recalculation, memory, repeatability and auditability.
7 min read
CSV
Using the sqlite3 dot import Command With a CSV File
Every option of the sqlite3 .import command that matters for CSV, from --csv and --skip to --schema temp and piped input, with the output of each.
7 min read
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
Get new articles by email
We send one email when a new article is published. No spam.