JSONGuide
Working With JSONB in PostgreSQL From First Query to Index
A working guide to JSONB in PostgreSQL 18: reading, filtering, updating, unnesting and aggregating documents, then GIN and expression indexes, with timings.
21 min read
Library
Everything you need to level up your coding skills.
XML
Should You Store XML in a Database Column or Shred It
Store XML whole, shred it into tables, or both? What each engine keeps and loses, what validation you get, and what it costs on 200,000 documents.
7 min read
CSV
Loading a CSV With a Header Row Into PostgreSQL Tables
Skip the header, or better, check it. See what HEADER true, HEADER match and a column list do, and the errors when the file and the table disagree.
7 min read
YAML
YAML Version Numbers Turning Into Floats and How to Stop It
Python 3.10 in a CI matrix loads as 3.1 in every parser. See the proof, why quoting is the fix and a script that finds suspect floats in a repository.
6 min read
Excel
Freezing Panes Filters and Column Widths From Code
Freeze the header row, add filter buttons and set column widths from Python with openpyxl and XlsxWriter, including an auto-width helper and print settings.
7 min read
CSV
Psql copy Versus Server Side COPY and Which One You Need
The two commands differ in which machine reads the file. See the permission errors, the path errors and the timings, and choose the one that fits.
7 min read
XMLGuide
Parsing and Writing XML in Python With ElementTree and lxml
A tested guide to XML in Python: ElementTree versus lxml, reading, streaming, writing, namespaces, validation, XSLT, tables, safety and an end-to-end script.
18 min read
JSON
Fixing JSON That Is Really a JavaScript Object Literal
Text copied from console.log, DevTools or a config file is often a JavaScript object, not JSON. What differs, what is lost, and how to convert it safely.
7 min read
Excel
Adding Formulas and Number Formats From Python Code
Write Excel formulas and number formats from Python with openpyxl and XlsxWriter: English syntax, cached values, table references, percent and date codes.
7 min read
CSV
Using COPY to Load a CSV File Into a PostgreSQL Table
The COPY command in practice: the options that matter, the two ways to feed it, what its row count means, and why one bad value undoes the whole load.
7 min read
Excel
Building a Multi Sheet Excel Report From SQL Queries
Build one Excel workbook from several SQL queries: a summary sheet, a sheet per region, formulas that point across sheets and an index with working links.
7 min read
Editor's pick
CSVGuide
How to Load a CSV File Into PostgreSQL Without Losing Rows
Load a CSV into PostgreSQL with COPY, see what makes it fast, and learn what each failure means, with a script that loads in one transaction.
20 min read
Get new articles by email
We send one email when a new article is published. No spam.