CSV
Skipping Bad Rows During a PostgreSQL CSV Import Safely
PostgreSQL 17 can skip rows with bad values during COPY. See ON_ERROR, REJECT_LIMIT and verbose logging in action, and the errors they do not cover.
7 min read
Tag
how-to
590 posts
XML
Converting XML to CSV for Spreadsheet Users Safely
Turn a nested XML file into a flat CSV with Python, joining repeated authors into one cell with correct quoting, and see why pandas read_xml dropped one.
7 min read
CSV
Handling NULL and Empty Strings in PostgreSQL CSV Imports
PostgreSQL loads an unquoted empty field as NULL and a quoted one as an empty string. See the options that change that and the integer error one causes.
8 min read
JSON
Querying JSONB With the Arrow Operators in PostgreSQL
How -> and ->> read keys and array elements in PostgreSQL JSONB, what comes back for missing keys, and the precedence and casting traps that bite.
8 min read
ExcelGuide
Reading and Writing Excel Files With openpyxl in Python
A working guide to openpyxl: the workbook model, reading values and formulas, writing and editing files, large-file modes, styles, charts and errors.
19 min read
PostgreSQL
Indexing Full Text Search With GIN in PostgreSQL Tables
Index PostgreSQL full text search with GIN: how it works, GIN versus GiST, fastupdate and the pending list, build time, size and when the planner skips it.
7 min read
XMLGuide
Converting XML to JSON CSV YAML and Excel Without Data Loss
One XML file converted to JSON, CSV, YAML and Excel with one parse and four writers, what each target loses, and how to prove nothing went missing.
20 min read
CSV
Loading a CSV Into a Staging Table Then Cleaning It in SQL
Load a messy CSV as text first, then convert it with SQL. Build a three format date parser, a reject table with reasons, and the typed insert.
8 min read
CSV
Importing Only Some CSV Columns Into a PostgreSQL Table
COPY cannot skip file columns. Compare two ways to load three of eight columns of a 2,000,000 row CSV, with timings, and see why cut breaks on quotes.
6 min read
XML
Loading a Product Feed in XML Into PostgreSQL With xmltable
Load a Google Merchant style product feed into PostgreSQL with xmltable and a namespace, split price from currency, and handle missing GTINs and empty prices.
7 min read
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
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