pgmonkey (7/8) — CSV Import and Export

Summary: Use pgmonkey’s pgexport and pgimport commands to move data between PostgreSQL tables and CSV files — with auto-detection of delimiters, encoding, and quoting, plus first-run config generation for repeatable imports.

Key	Value
pgmonkey version	4.0.0
Python version	3.12
Config file	pgmonkey_config.yaml
Database name	tutorial_db
Export table	public.customers
Export file	customers_export.csv
Import file	new_customers.csv

0. Prerequisites

• Completed Part 1 (pgmonkey installed, config file working)
• A PostgreSQL table with data to export (or follow step 1 to create one)

---

1. Create a Sample Table

If you do not have a table to work with, create one.

CREATE TABLE public.customers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    city TEXT,
    created_at TIMESTAMP DEFAULT now()
);

INSERT INTO public.customers (name, email, city) VALUES
    ('Alice Chen', '[email protected]', 'Portland'),
    ('Bob Martinez', '[email protected]', 'Austin'),
    ('Carol Smith', '[email protected]', 'Denver');




Code language: PHP (php)

Run this SQL through any PostgreSQL client — psql, pgAdmin, or a pgmonkey script from Part 1.

---

2. Export a Table to CSV

Use pgexport to write a table to a CSV file.

pgmonkey pgexport --table public.customers --connconfig pgmonkey_config.yaml --export_file customers_export.csv




Code language: CSS (css)

First run: pgmonkey creates an export config file (customers_export.csv.export_config.yaml) alongside the CSV. This config records the delimiter, quote character, and encoding used for the export.

Subsequent runs: pgmonkey reads the existing config file and uses the same settings, ensuring consistency.

Check the output.

head customers_export.csv




Code language: CSS (css)

id,name,email,city,created_at
1,Alice Chen,alice@example.com,Portland,2026-02-17 10:30:00
2,Bob Martinez,[email protected],Austin,2026-02-17 10:30:00
3,Carol Smith,[email protected],Denver,2026-02-17 10:30:00




Code language: CSS (css)

The export uses PostgreSQL’s COPY TO protocol for fast bulk transfer. A progress bar tracks the operation for large tables.

---

3. Import a CSV into a Table

Create a CSV file to import. Save this as new_customers.csv.

name,email,city
Diana Lee,diana@example.com,Seattle
Eric Johnson,[email protected],Chicago




Code language: CSS (css)

Import it into a new table.

pgmonkey pgimport --table public.new_customers --connconfig pgmonkey_config.yaml --import_file new_customers.csv




Code language: CSS (css)

First run: pgmonkey creates an import config file (new_customers.csv.import_config.yaml). The config includes auto-detected settings: delimiter, quote character, encoding, and whether the file has headers.

Tip: If the target table does not exist, set auto_create_table: true in the import config to have pgmonkey create it based on the CSV headers and inferred types.

---

4. The Auto-Detection Engine

When pgmonkey encounters a CSV file for the first time, it inspects the file to determine:

Setting	How it is detected
Encoding	BOM (byte-order mark) detection first, then chardet library fallback
Delimiter	Python’s csv.Sniffer analyzes a sample of the file
Quote character	csv.Sniffer detects the quoting convention
Has headers	csv.Sniffer determines if the first row is a header

These detected values are written to the config file so you can review and override them before running the import again.

---

5. The Import Config File

After the first import attempt, pgmonkey generates a config file. Open it to review the auto-detected settings.

has_headers: true
auto_create_table: false
enforce_lowercase: true
delimiter: ','
quotechar: '"'
encoding: 'utf-8'




Code language: HTTP (http)

Setting	Purpose
has_headers	Whether the first row is a header row
auto_create_table	Create the target table if it does not exist
enforce_lowercase	Convert column names to lowercase
delimiter	Field separator character
quotechar	Character used to quote fields containing the delimiter
encoding	File encoding (detected automatically)

Edit this file to adjust any setting, then run the import command again. pgmonkey reads the config and uses your values instead of auto-detecting.

---

6. Handling Schema-Qualified Tables

pgmonkey supports schema.table notation for both import and export.

pgmonkey pgexport --table myschema.orders --connconfig pgmonkey_config.yaml --export_file orders.csv




Code language: CSS (css)

pgmonkey pgimport --table myschema.orders --connconfig pgmonkey_config.yaml --import_file orders.csv




Code language: CSS (css)

Table and schema names are safely quoted using psycopg.sql — no risk of SQL injection from table names containing special characters.

---

7. Roundtrip Workflow

A common pattern is exporting from one database and importing into another.

Export from source:

pgmonkey pgexport --table public.customers --connconfig source_config.yaml --export_file customers.csv




Code language: CSS (css)

Import to destination:

pgmonkey pgimport --table public.customers --connconfig dest_config.yaml --import_file customers.csv




Code language: CSS (css)

The export config ensures the CSV format is consistent, and the import config ensures the destination table is populated correctly. For large datasets, both operations use PostgreSQL’s COPY protocol, which is significantly faster than row-by-row inserts.

---

8. Export Without a Filename

If you omit --export_file, pgmonkey generates a filename based on the table name.

pgmonkey pgexport --table public.customers --connconfig pgmonkey_config.yaml




Code language: CSS (css)

This creates public.customers.csv (or similar) in the current directory.

---

Summary

You exported and imported CSV data using pgmonkey’s CLI.

• pgexport writes a PostgreSQL table to CSV using the COPY protocol
• pgimport loads a CSV file into a PostgreSQL table
• Auto-detection handles encoding, delimiters, quoting, and headers
• Config files are generated on first run for repeatable operations
• Schema-qualified table names are fully supported
• Both operations use bulk transfer for performance

Next up: pgmonkey (8/8) — Production Patterns ties everything together with transaction management, GUC session settings, connection caching, Flask and FastAPI integration, and a production readiness checklist.