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.

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');

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

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

id,name,email,city,created_at
1,Alice Chen,[email protected],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

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,[email protected],Seattle
Eric Johnson,[email protected],Chicago

Import it into a new table.

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

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:

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'

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

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

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

Import to destination:

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

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

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.