Skip to main content

pg-upsert

ci/cd codecov Documentation Status PyPI Latest Release PyPI Downloads Python Version Support

pg-upsert is a Python package for validating and upserting data from staging tables into base tables in PostgreSQL. It runs automated QA checks, reports errors with rich formatted output, and performs dependency-aware upserts.

An upsert (a blend of update and insert, standardized in SQL as MERGE) updates a row if it already exists and inserts it if it does not. pg-upsert matches staging rows to base rows by primary key — matched rows update the base table, unmatched rows are inserted — all inside a single transaction that is rolled back unless you ask to commit.

pg-upsert terminal output with interactive compare table

Why Use pg-upsert?

  • 8 Automated QA Checks – Validates NOT NULL, PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK CONSTRAINT, bounded character length, column existence, and column type compatibility before any modifications occur.
  • Interactive Confirmation – Two UI backends: Textual TUI (terminal) and Tkinter (desktop). Auto-detected or choose with --ui auto|textual|tkinter. The compare-tables dialog includes a Highlight Diffs toggle that tints matching/changed rows and flags the exact cells that differ, skipping any columns excluded from the upsert.
  • Structured Results – run() returns an UpsertResult with per-table stats, QA errors, and JSON serialization (--output=json for CI/CD pipelines).
  • Exportable Fix Sheets – --export-failures <dir> writes an actionable report of failing rows: one row per unique violating staging row with an _issues column listing every problem (NULL in 'genre', duplicate PK, FK violation, etc.) so users can open it in Excel and fix the data. Supports CSV (file per table), JSON (nested), and XLSX (sheets per table) via --export-format.
  • Schema Validation – --check-schema validates column existence and type compatibility without scanning staging data or running upserts.
  • Flexible Upsert Strategies – Supports upsert, update, and insert methods.
  • YAML Configuration – One config file drives both the CLI (--config-file) and the Python API (PgUpsert.from_config()), with layered sources and per-table column excludes.
  • Dependency-Aware Ordering – Tables are processed in FK dependency order automatically.
  • Rich Output – Colored pass/fail indicators, formatted tables, and dual console+logfile output.

Installation

Requires Python 3.10+ and a PostgreSQL database.

pip install pg-upsert

Optional extras:

pip install "pg-upsert[tui]"   # Textual terminal UI for interactive mode
pip install "pg-upsert[xlsx]"  # XLSX fix-sheet export

A prebuilt Docker image is also available — see Docker.

Usage

Python API

from pg_upsert import PgUpsert

result = PgUpsert(
    uri="postgresql://user@localhost:5432/mydb",
    tables=("genres", "publishers", "books", "authors", "book_authors"),
    staging_schema="staging",
    base_schema="public",
    do_commit=True,
    upsert_method="upsert",
    exclude_cols=("rev_user", "rev_time"),
    exclude_null_check_cols=("book_alias",),
    exclude_cols_by_table={"books": ("isbn_legacy",)},
    exclude_null_check_cols_by_table={"books": ("reprint_date",)},
).run()

# UpsertResult provides structured access to results
print(result.qa_passed)       # True if all QA checks passed
print(result.committed)       # True if changes were committed
print(result.total_updated)   # Total rows updated across all tables
print(result.total_inserted)  # Total rows inserted across all tables
print(result.to_json())       # JSON serialization for CI/CD

exclude_cols and exclude_null_check_cols apply to every table. The *_by_table mappings add per-table excludes merged on top of the global lists; each key must be one of the configured tables.

From a configuration file (the same YAML file the CLI accepts with --config-file):

from pg_upsert import PgUpsert

result = PgUpsert.from_config("pg-upsert.yaml").run()

# Keyword overrides beat file values, and multiple files layer left-to-right
result = PgUpsert.from_config(["base.yaml", "task.yaml"], do_commit=True).run()

Using an existing connection:

import psycopg
from pg_upsert import PgUpsert

conn = psycopg.connect(host="localhost", port=5432, dbname="mydb", user="user", password="pass")

ups = PgUpsert(
    conn=conn,
    tables=("genres", "publishers", "books"),
    staging_schema="staging",
    base_schema="public",
    do_commit=True,
)
result = ups.run()

# Drop all ups_* temp objects (connection stays open)
ups.cleanup()

QA-only mode (no upsert):

from pg_upsert import PgUpsert

ups = PgUpsert(
    uri="postgresql://user@localhost:5432/mydb",
    tables=("genres", "books"),
    staging_schema="staging",
    base_schema="public",
).qa_all()

# qa_passed is False only when ERROR-level findings exist.
# qa_errors returns ERROR findings only (block the upsert).
# qa_warnings returns WARNING findings only (informational, do not block).
# qa_findings returns all findings combined.
# qa_passed may be True while qa_warnings is non-empty.
if not ups.qa_passed:
    for err in ups.qa_errors:
        print(f"{err.table}: {err.check_type.value} — {err.details}")

Schema compatibility check (column existence and type mismatches only):

from pg_upsert import PgUpsert

ups = PgUpsert(
    uri="postgresql://user@localhost:5432/mydb",
    tables=("genres", "books"),
    staging_schema="staging",
    base_schema="public",
).qa_column_existence().qa_type_mismatch()

if ups.qa_errors:
    for err in ups.qa_errors:
        print(f"{err.table}: {err.check_type.value} — {err.details}")
else:
    print("Schemas are compatible")

Pipeline callbacks (per-table progress):

from pg_upsert import PgUpsert, CallbackEvent

def on_event(event):
    if event.event == CallbackEvent.QA_TABLE_COMPLETE:
        print(f"QA {'passed' if event.qa_passed else 'failed'} for {event.table}")
    elif event.event == CallbackEvent.UPSERT_TABLE_COMPLETE:
        print(f"{event.table}: {event.rows_inserted} inserted, {event.rows_updated} updated")

result = PgUpsert(
    uri="postgresql://user@localhost:5432/mydb",
    tables=("genres", "books"),
    staging_schema="staging",
    base_schema="public",
    do_commit=True,
    callback=on_event,
).run()

CLI

pg-upsert -h localhost -p 5432 -d mydb -u user \
  -s staging -b public \
  -t genres -t publishers -t books -t authors -t book_authors \
  -x rev_user -x rev_time \
  --commit
Option Description
-h, --host Database host
-p, --port Database port (default: 5432)
-d, --database Database name
-u, --user Database user (see Authentication)
-s, --staging-schema Staging schema name (default: staging)
-b, --base-schema Base schema name (default: public)
-e, --encoding Database connection encoding (default: utf-8)
-t, --table Table name to process (repeatable)
-x, --exclude-columns Columns to exclude from upsert (repeatable)
-n, --null-columns Columns to skip during NOT NULL checks (repeatable)
-m, --upsert-method upsert, update, or insert (default: upsert)
-c, --commit Commit changes (default: roll back)
-i, --interactive Prompt for confirmation at each step
-l, --logfile Write log to file (appends, does not overwrite)
-o, --output Output format: text (default) or json
--check-schema Validate column existence and types only, then exit
--compact Use compact grid format for QA summary
--ui Interactive UI: auto (default), textual, or tkinter
--export-failures Directory to write a QA failure fix sheet into
--export-format Fix sheet format: csv (default), json, or xlsx
--export-max-rows Max rows to capture per check per table (default 1000)
--strict-columns Treat all missing staging columns as errors
-f, --config-file Path to YAML configuration file
-g, --generate-config Generate a template config file
-v, --version Show version and exit
--docs Open documentation in browser
--debug Enable debug output

Configuration File

Create a YAML config file (see pg-upsert.example.yaml):

debug: false
commit: false
interactive: false
upsert_method: "upsert"  # Options: "upsert", "insert", "update"
logfile: "pg_upsert.log"
host: "localhost"
port: 5432
user: "docker"
database: "dev"
staging_schema: "staging"
base_schema: "public"
encoding: "utf-8"
tables:
  - "authors"
  - "publishers"
  - "books"
  - "book_authors"
  - "genres"
exclude_columns:
  - "rev_time"
  - "rev_user"
null_columns:
  - "book_alias"
exclude_columns_by_table:  # Per-table excludes, merged on top of exclude_columns
  books:
    - "isbn_legacy"
null_columns_by_table:  # Per-table NOT NULL check skips, merged on top of null_columns
  books:
    - "reprint_date"
output: "text"  # Options: "text", "json"
check_schema: false
compact: false
ui_mode: "auto"  # Options: "auto", "textual", "tkinter"
export_failures: null  # Directory to write QA failure fix sheet; null to disable
export_format: "csv"  # Fix sheet format: "csv", "json", or "xlsx"
export_max_rows: 1000  # Max rows captured per check per table for the fix sheet
strict_columns: false  # Treat all missing staging columns as errors

Run with: pg-upsert -f config.yaml, or load the same file in Python with PgUpsert.from_config("config.yaml").

Docker

docker run -it --rm \
  -v $(pwd):/app \
  ghcr.io/geocoug/pg-upsert:latest \
  -h host.docker.internal -p 5432 -d dev -u docker \
  -s staging -b public -t genres --commit

QA Checks

pg-upsert runs 8 types of QA checks on staging data before upserting:

Check What it validates
Column Existence PK and NOT NULL (no default) columns must exist in staging (error); other missing columns produce warnings. Use --strict-columns for strict mode.
Column Type No hard type incompatibilities between staging and base (uses PostgreSQL's pg_cast catalog)
Character Length Values fit bounded base varchar(n) and char(n) columns; excluded columns and NULL values are skipped
NOT NULL Non-nullable base columns have no NULL values in staging
Primary Key No duplicate values in PK columns
Unique No duplicate keys in UNIQUE constraints or unique indexes, within staging or against existing base rows (NULLs allowed)
Foreign Key All FK references point to rows that will exist in the referenced table after the load
Check Constraint All CHECK constraint expressions evaluate to true

Data checks only look at the rows your upsert_method will actually write, and use base values for exclude_cols in updated rows. See How QA Models the Load.

See the QA Checks Reference for detailed documentation.

--check-schema remains metadata-only: it checks column existence and type compatibility but does not scan staging values for length violations. Run normal QA with qa_all() or call qa_all_length() / qa_length(table) when you need character-length validation.

Authentication

pg-upsert resolves the database password in this order:

  1. Password in URI (Python API only) — postgresql://user:pass@host/db
  2. PGPASSWORD environment variable — standard PostgreSQL convention, works with both CLI and API
  3. Interactive prompt — if neither of the above is set

For CI/CD pipelines, use PGPASSWORD to avoid interactive prompts:

PGPASSWORD=secret pg-upsert -h host -d db -u user \
  -s staging -b public -t books \
  --output json --commit

pg-upsert also supports PostgreSQL's .pgpass file via psycopg (libpq).

Exit Codes

Code Meaning
0 QA passed and upsert completed (or user cancelled)
1 QA failed, schema check failed, or error

Contributing

See CONTRIBUTING.md for development setup, available recipes, testing, and release process.

git clone https://github.com/geocoug/pg-upsert
cd pg-upsert
just sync
just test

Metadata

Release files for pg-upsert 1.25.0

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for pg-upsert 1.25.0
File Size Uploaded Provenance Actions
pg_upsert-1.25.0.tar.gz 1.6 MB With provenance Details for pg_upsert-1.25.0.tar.gz

Built distribution (wheel)

Table of built distributions (wheels) for pg-upsert 1.25.0
File Interpreter ABI Platform Actions
pg_upsert-1.25.0-py3-none-any.whl Python 3 none any Details for pg_upsert-1.25.0-py3-none-any.whl

Total release size: 1.7 MB

Release files / pg_upsert-1.25.0.tar.gz

Download URL pg_upsert-1.25.0.tar.gz
Size 1.6 MB
Tags Source
SHA-256 checksum
How to use checksums
82f66f45e88e7495a371f46720f6380ca1a3376c49cb6d91faedb5ed84036c9b
BLAKE2b-256 checksum
How to use checksums
782c5c8e325c881be87273f48f8e5c2f92d0f9932a42c5e21d8f94e6ce2d1563
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via twine/7.0.0 CPython/3.13.14

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Oct 1, 2026.

Transparency log

Release files / pg_upsert-1.25.0-py3-none-any.whl

Download URL pg_upsert-1.25.0-py3-none-any.whl
Size 108.9 kB
Tags Python 3
SHA-256 checksum
How to use checksums
1b0a7cf3537680e1292dba0dcb90cd9efbc3107be512557ab920fefd387d6a43
BLAKE2b-256 checksum
How to use checksums
1d14192fe8f221ffa5e6cddc4f85e861a530543878371f4bcf3d8ac6b1b8f4f2
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via twine/7.0.0 CPython/3.13.14

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Oct 1, 2026.

Transparency log

Security for pg-upsert 1.25.0

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

Full provenance

Both files in this release have been uploaded with provenance metadata.

Select from 2 files

Attestation for pg_upsert-1.25.0.tar.gz

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Oct 1, 2026.

Transparency log

A note on package security

PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.

To learn more about protecting yourself from malicious actors, visit our package security documentation.

Release history Release notifications | RSS feed

This release

1.25.0 This release

2 release files Full provenance

Anthropic, PBC Visionary sponsor Bloomberg Visionary sponsor Hudson River Trading Visionary sponsor Meta Visionary sponsor NVIDIA Visionary sponsor Microsoft Sustainability sponsor Depot Continuous Integration AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Sentry Error logging StatusPage Status page