Upserting , the ability to insert a row or update it if it already exists , is an essential pattern when writing resilient data pipelines and services. With PostgreSQL’s robust ON CONFLICT clause and SQLAlchemy’s integration with dialect-specific features, developers can implement upserts in a clear and efficient way.
This article explains how to use PostgreSQL upsert patterns with SQLAlchemy, covering both Core and ORM approaches, practical patterns, and common pitfalls to avoid. The goal is to give actionable guidance so you can pick the right strategy for your application.
Understanding Upsert Semantics
Upsert combines two operations: INSERT and UPDATE. The database attempts to insert a row and, if a uniqueness conflict occurs, performs an UPDATE instead. PostgreSQL implements this via the ON CONFLICT clause introduced in version 9.5, which allows specifying a conflict target and the update behavior.
Key concepts in PostgreSQL upsert include the conflict target (columns or constraint) and the special excluded pseudo-table, which holds the values originally proposed for insertion. These allow you to write expressive rules such as updating only certain columns or skipping the update entirely.
Understanding atomicity and side effects is important: an upsert is a single statement, so it avoids race conditions that arise when doing separate SELECT/INSERT/UPDATE steps. However, you must still consider triggers, RETURNING clauses, and how default values are applied on conflict.
Why Use Upsert with PostgreSQL
Upserts are useful when you need idempotent writes in distributed systems, background jobs, or ETL processes. Instead of checking for existence in application code, you can delegate conflict resolution to the database for better performance and simplicity.
PostgreSQL’s ON CONFLICT is flexible: you can choose to DO NOTHING, DO UPDATE with expressions that reference excluded values, or even use conditional updates. This gives you control over how duplicates are resolved without extra roundtrips to the database.
Using database-native upserts also centralizes business logic related to uniqueness constraints, which can reduce bugs and ensure consistent behavior across clients and services interacting with the same database.
SQLAlchemy Core vs ORM Approaches
SQLAlchemy Core exposes the SQL-level constructs directly and is the most explicit way to use PostgreSQL upsert features. The dialect helper insert(…) from sqlalchemy.dialects.postgresql provides an on_conflict_do_update() method to express upserts.
The ORM can also perform upserts, typically by creating an insert() statement with dialect-specific behavior and executing it via session.execute(). Alternatively, SQLAlchemy’s Session.merge() can perform an upsert-like merge, but it has different semantics: merge loads state and can be less efficient for high-throughput scenarios.
Choosing between Core and ORM depends on your needs: use Core constructs for bulk operations and fine-grained control, and use ORM-friendly patterns when you want integration with mapped objects and unit-of-work semantics. In many cases you can blend both, building a dialect-specific insert and then using the session to execute it.
Using ON CONFLICT with SQLAlchemy Core
With SQLAlchemy Core, the typical pattern is to import the PostgreSQL insert construct and call on_conflict_do_update. You specify the target columns or constraint and provide a mapping or SQL expressions for values to update when a conflict occurs.
A common approach is to use the excluded alias to refer to the insert values. For example, you can set column = excluded.column to overwrite existing values, or use more complex SQL functions to merge arrays, sums, or timestamps. The Core API accepts ColumnElements, letting you express arbitrary SQL logic.
Core upserts work well for bulk inserts because you can pass multiple rows to the insert statement and perform a single network roundtrip. Remember to include RETURNING if you need generated primary keys or other computed values back after the operation.
ORM-friendly Upsert Patterns
In ORM contexts, you often want to keep working with mapped objects. A pragmatic pattern is to build a dialect-specific insert statement with on_conflict_do_update and then call session.execute(insert_stmt) followed by session.commit(). This avoids loading objects from the database and keeps operations efficient.
If you prefer object-level semantics, Session.merge() can be used to merge a detached instance into the session and persist changes. Merge issues SELECTs to determine whether to INSERT or UPDATE, which makes it less suitable for high-volume upserts but convenient for smaller transactional flows.
Another practical pattern is to encapsulate upsert logic in repository functions or helper utilities that accept dictionaries or dataclass objects, generate the insert/on_conflict statement, and return ORM instances if needed. This keeps your domain layer decoupled from SQL details while still leveraging PostgreSQL’s upsert features.
Common Pitfalls and Best Practices
One pitfall is assuming ON CONFLICT automatically updates all columns; you must explicitly specify which columns to update. Forgetting to handle excluded values or timestamps can produce stale data. Be deliberate about which fields are overwritten and which are left unchanged.
Be mindful of unique constraints and indexes: the conflict target must match a unique index or constraint. If you use a partial unique index or expression index, ensure your conflict target is appropriate; otherwise, the upsert will not trigger as expected.
For bulk upserts, measure performance and watch for lock contention on hot rows. Use batched operations, consider partitioning strategies, and when necessary, add optimistic concurrency checks or conflict handling to avoid long-running transactions that cause bottlenecks.
Error Handling and Diagnostics
When an upsert fails, PostgreSQL typically reports constraint violations or other SQL errors. In SQLAlchemy, these surface as database-specific exceptions that you can catch and inspect. For example, IntegrityError indicates a constraint problem that you may want to log and handle gracefully.
Logging the SQL and parameters for failed upserts can be invaluable when diagnosing issues. Enable SQLAlchemy’s echo or use structured logging in your DB access layer to capture statement text, bind parameters, and stack traces for problematic operations.
Test upsert behavior thoroughly, including edge cases like concurrent inserts, NULL values in unique columns, and partial index interactions. Unit and integration tests will help ensure your upsert logic behaves deterministically under load.
When Not to Use Upsert
Upsert is not always the right tool. If your business logic requires complex merging rules or multi-row transactions that depend on other tables, handling updates in application code with explicit transactions might be clearer and safer.
Also, if you need detailed audit trails of every change, a simple upsert that overwrites values may obscure historical data. In such cases, consider append-only patterns, versioned rows, or explicit change logging tables rather than silent upserts.
Finally, avoid upserts when performance profiling shows contention or excessive write amplification. Profiling and load testing will reveal whether an upsert-based approach scales for your workload.
Implementing PostgreSQL upserts with SQLAlchemy gives you a powerful combination: database-level conflict resolution and a flexible Python ORM/Core layer. By choosing the right API (Core or ORM) and carefully specifying conflict behavior, you can write efficient, maintainable code.
Keep in mind the trade-offs, test thoroughly under realistic conditions, and encapsulate upsert logic in well-documented helpers or repositories. With these practices, SQLAlchemy PostgreSQL upsert patterns can simplify your code and improve reliability.
