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
Library
Everything you need to level up your coding skills.
Excel
Running Totals in Excel Versus Window Functions in SQL
Replace copied running-total formulas with SQL window functions: sums per group, moving averages, differences, ranks, and the tie trap in the default frame.
7 min read
CSV
Fixing Incorrect Date Value Errors in a MySQL CSV Import
MySQL rejects a US date with error 1292 on a server load and stores a zero date on a LOCAL load. See both, the STR_TO_DATE fix, and how to find zero dates.
9 min read
XML
Writing a Basic XSD Schema That Rejects Bad Orders
Build an XSD for an orders file step by step: sequence, pattern, enumeration, optional elements, required attributes and a decimal with attributes.
7 min read
YAML
Why yaml load Is Dangerous and What to Use Instead
How a YAML tag makes PyYAML run code during load, a harmless demonstration, what changed in PyYAML 6 and the safe options for loading untrusted files.
6 min read
CSV
Mapping CSV Columns to Table Columns With LOAD DATA
Skip, reorder and compute columns in LOAD DATA with a column list, user variables and SET, plus the silent shift a short column list causes.
7 min read
Excel
INDEX and MATCH Versus a Join on a Key in SQL Compared
Translate INDEX and MATCH into SQL: one-way lookups, two-way table lookups, left-of-key lookups, match types and the join that replaces a lookup grid.
7 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
XMLGuide
Validating XML Against an XSD Schema Step by Step Guide
How to check XML files against an XSD: write the schema, run xmllint and lxml, read the errors, add Schematron for business rules, and test changes safely.
18 min read
CSV
Importing a CSV Into MySQL With a Header Row and Odd Quotes
Skip a header with IGNORE 1 LINES, then handle doubled quotes, backslashes, embedded newlines and Windows line endings that LOAD DATA treats differently.
7 min read
Excel
Pivot Tables Versus GROUP BY and CASE in SQL Compared
Rebuild an Excel pivot table in SQL with GROUP BY, conditional aggregation, ROLLUP and window percentages, and see what changes when columns are fixed.
7 min read
CSV
MySQL LOAD DATA LOCAL INFILE Disabled and How to Enable It
Two switches control LOAD DATA LOCAL, one on the server and one on the client, each failing with its own error code. Enable both safely and know the risk.
6 min read
Get new articles by email
We send one email when a new article is published. No spam.