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
Tag
how-to
590 posts
Editor's pick
CSVGuide
Importing CSV Files Into SQLite From the Command Line
The sqlite3 .import command loads a CSV in seconds and reports problems as warnings with exit status zero. Import safely with types, checks and a swap.
20 min read
YAMLGuide
Working With YAML From the Terminal Using yq and Friends
A tested command-line guide to YAML: query, edit, merge, convert, lint, validate and diff with yq, yamllint and check-jsonschema, plus the traps found.
20 min read
JSON
Expanding JSONB Into Typed Rows With jsonb_to_recordset
Turn a JSONB array of objects into typed SQL columns with jsonb_to_recordset, jsonb_populate_recordset and jsonb_each, with the errors and edge cases.
7 min read
CSV
Importing a Large CSV Into MySQL Without Locking the Table
Measure what other sessions see while LOAD DATA runs on InnoDB, whether chunking helps, and how a build and swap with RENAME TABLE avoids any visible window.
8 min read
PostgreSQL
Typo Tolerant Search With pg_trgm in PostgreSQL Queries
Build typo tolerant search with pg_trgm in PostgreSQL: trigrams, similarity thresholds, GIN and GiST indexes, word similarity and full text fallback.
7 min read
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
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
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
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