load-to-postgres

name	load-to-postgres
description	Use when you need to load a generated taxonomy into Postgres — create table, load data, support upsert/replace/append modes, log the operation.

Load Taxonomy to Postgres

Load a generated CSV/JSON taxonomy into a Postgres database. Generate DDL, choose load mode, execute with logging.

When to use

• "Load this taxonomy into my Postgres database"
• "I want to upsert the countries table into Postgres"
• "Create a Postgres table and load the taxonomy"

Inputs to gather

• Connection: DSN string (postgresql://user:pass@host:port/dbname) or rely on PG* env vars (PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE).
• Table name: What to call the table in Postgres.
• Schema: Default public; override if needed.
• Taxonomy file path: Absolute path to CSV or auto-discover from data/<name>/<name>.csv.
• Load mode: replace (TRUNCATE + load), upsert (INSERT … ON CONFLICT DO UPDATE), append (INSERT only).

Procedure

1. Generate DDL: Inspect the taxonomy CSV columns; infer types:
   • code → VARCHAR(32) or VARCHAR(64), PRIMARY KEY.
   • label / name → VARCHAR(255).
   • Numeric IDs → INTEGER or BIGINT.
   • metadata / JSON fields → JSONB.
   • parent_code → VARCHAR(32) with FOREIGN KEY constraint (DEFERRABLE INITIALLY DEFERRED if hierarchical).
   • Timestamps → TIMESTAMPTZ DEFAULT now().
   • Add created_at TIMESTAMPTZ DEFAULT now() and updated_at TIMESTAMPTZ DEFAULT now() if not present.
2. Show the user the generated DDL before executing. Ask for confirmation if adding/modifying columns.
3. Choose load path:
   • For large files (>10k rows): Use \copy via psql (fast, streaming).
   • For small files: Row-by-row INSERT via psycopg2 (Python) for full type control and error reporting.
4. Handle load mode:
   • replace: TRUNCATE TABLE <table> CASCADE; \copy <table> FROM '<csv_path>' WITH (FORMAT csv, HEADER, DELIMITER ',');
   • upsert: INSERT INTO <table> (columns) SELECT * FROM (columns from CSV) ON CONFLICT (code) DO UPDATE SET (columns) = EXCLUDED.(columns);
   • append: \copy <table> FROM '<csv_path>' WITH (FORMAT csv, HEADER, DELIMITER ','); (assumes table exists).
5. Hierarchical handling: If parent_code column exists and has FK constraints, either (a) load with SET CONSTRAINTS ALL DEFERRED and re-enable after, or (b) use topological ordering (load roots first).
6. Log the operation: Write to state/loads/<timestamp>-<schema>.<table>.log:
   • Timestamp, mode, table, row count, status (success/error), any conflict count (for upsert).
   • Include the exact SQL command executed (sanitized — no password).
7. Report to user: Row count loaded, any conflicts (upsert), confirmation of success.

Output / side effects

• Table created/updated in Postgres.
• Load logged to state/loads/<timestamp>-<schema>.<table>.log.

Safety / constraints

• No credentials stored: Use PG* env vars or .pgpass for auth; never write passwords to logs.
• Destructive mode: Confirm before TRUNCATE (replace mode).
• Foreign keys: Defer FK checks if loading hierarchical data; re-enable after.