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
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.
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
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
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
CSV
Checking a CSV for Invalid UTF8 Before You Load It
Find the lines and bytes that break a UTF-8 load with iconv, isutf8, grep or a short Python script, and see how PostgreSQL and DuckDB report them.
7 min read
Excel
Exporting MySQL Data to Excel With a Python Script
Export a MySQL query to a clean XLSX with PyMySQL and pandas, handling DECIMAL, zero dates, tinyint flags and utf8mb4, and compare it with Workbench.
7 min read
Excel
Exporting SQL Server Query Output to an Excel File
Export SQL Server query results to XLSX with pyodbc and pandas, and see what SSMS, bcp and the Import and Export Wizard give you instead.
7 min read
Excel
Writing a DataFrame to Excel With Formatting in pandas
Format a pandas DataFrame in Excel with XlsxWriter: header style, number and date formats, auto-fit widths, a table with banding and conditional formatting.
7 min read
Get new articles by email
We send one email when a new article is published. No spam.