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
Topic
PostgreSQL
The database that keeps saying yes: JSONB, window functions, extensions, and the tricks that make it run like it has more cores than it does.
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
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
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
Excel
Why an Exported Excel Date Shows as a Number and the Fix
An exported date shows as 45292 because Excel stores dates as numbers and the cell has no date format. See the cause, the code fix and a file repair.
7 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
JSON
JSON Versus JSONB in PostgreSQL and Which to Store
PostgreSQL has two JSON types. What each keeps, drops and costs, measured on 200,000 rows, and the narrow cases where plain json is the right choice.
7 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
Excel
Making Excel Exports Open Cleanly Every Time on Any PC
Stop repair prompts, garbled accents and wrong columns when exports open in Excel: legal characters, sheet names, time zones, NaN, BOM and separators.
7 min read
CSV
PostgreSQL Invalid Byte Sequence for UTF8 in a CSV Import
COPY stops with an invalid byte sequence error when a CSV is Latin 1. Fix it with the ENCODING option or client_encoding, and avoid the SQL_ASCII trap.
7 min read
CSV
PostgreSQL COPY Error Extra Data After Last Expected Column
The COPY error means a row has more fields than the table has columns. See the four usual causes, how to find the line, and the fix for each one.
7 min read
Excel
Emailing a Scheduled Excel Report From a Database Job
Build a job that queries a database, writes an Excel file and emails it on a schedule with smtplib and cron, with safe credentials and failure alerts.
8 min read
Get new articles by email
We send one email when a new article is published. No spam.