PostgreSQL – Equivalents to Oracle DML Error Logging

Baseline Statement

PostgreSQL does not provide a native, engine-level equivalent to Oracle DML Error Logging (LOG ERRORS ... REJECT LIMIT ...) for INSERT, UPDATE, DELETE, or MERGE.

In PostgreSQL, when a set-based DML statement encounters an error for any row, the default behavior is that the entire statement fails. If the statement is executed inside an explicit transaction block, the transaction enters an error state and must be rolled back before further commands can succeed.

What Oracle Provides (Reference Behavior)

Oracle DML Error Logging provides the following behavior in one statement:

1. Attempt to apply a set-based DML operation.
2. For each row:
• successful rows are applied to the target
• failed rows are skipped
3. Failed rows are written to an error log table with diagnostic metadata.
4. The statement continues until completion or until a reject limit is reached.
5. Statement-level or fatal errors are raised as exceptions and are not written to the error log table.

Equivalent Patterns in PostgreSQL

1. Pre-validation and Data Filtering (Set-based Reject by Rules)

Description

Invalid rows are excluded before they reach the target table. Reject logging is implemented by explicitly inserting excluded rows into a separate reject table.

This approach is set-based and fast, but it only works for validation rules that can be expressed in SQL predicates.

Typical Implementation

• Insert valid rows using WHERE predicates.
• Insert invalid rows into a reject table using the inverse predicate, optionally assigning a reason.

Disadvantages

1. Rule coverage is limited to validations that can be expressed in SQL conditions.
2. It does not automatically capture constraint failures that depend on target state or concurrency.

2. Handling Uniqueness Conflicts with ON CONFLICT (Partial Equivalent)

Description

PostgreSQL supports INSERT ... ON CONFLICT for handling unique constraint conflicts without failing the statement. This can be used to approximate Oracle behavior for the specific case of unique or primary key violations.

This pattern can be combined with separate reject logging to record rows skipped due to conflict.

Disadvantages

1. It only addresses conflicts covered by unique indexes or constraints.
2. It does not handle other constraint errors (CHECK, NOT NULL, FK) or conversion errors.

3. Row-by-Row Exception Capture in PL/pgSQL (Closest Functional Behavior)

Description

A procedure iterates over input rows, attempts to apply DML per row, and catches exceptions per row. Failed rows are written to a reject table, and processing continues.

This is the closest approximation to Oracle’s “log-and-continue” behavior, because it captures real database errors and continues execution.

Disadvantages

1. It is procedural and not set-based, leading to significantly lower throughput for large datasets.
2. It increases code complexity and maintenance effort.
3. Transaction and locking behavior must be managed carefully for large batches.

4. Two-Phase Load via Staging (Typed Target Load with Reject Logging)

Description

Data is first loaded into a staging table designed for ingestion (often with permissive types, such as text). A second phase performs validation and type conversion into the target schema, writing rejected rows into a reject table.

This is the most common pattern for production ETL pipelines in PostgreSQL, particularly when data arrives from files or external systems.

Disadvantages

1. Requires additional storage and additional pipeline steps.
2. Reject classification depends on explicit validation logic, not automatic error capture.

Error vs Exception Handling in PostgreSQL (Important Distinction)

Oracle separates “row-level DML errors that can be logged” from “statement-level fatal errors that must be raised.” PostgreSQL does not provide a comparable engine-level routing mechanism during set-based DML.

In PostgreSQL:

• Set-based DML errors are exceptions that terminate the statement.
• There is no built-in row-level logging table populated by the engine.

Comparative Summary (PostgreSQL Perspective)

PostgreSQL does not support SQL-native row-level reject logging with continuation in a single DML statement. Equivalent behavior requires one of the following:

1. Set-based pre-validation plus explicit reject logging (fast, limited coverage).
2. ON CONFLICT patterns for uniqueness conflicts (partial coverage).
3. Procedural row-by-row processing with exception capture (closest behavior, slowest).
4. Staging-based ETL flows (operationally robust, multi-step).

Final Conclusion

PostgreSQL provides no direct equivalent to Oracle DML Error Logging. Its alternatives are architectural patterns rather than a single database feature. The correct approach depends on the required scope of rejection handling:

• If the primary issue is duplicate keys, ON CONFLICT is the most practical partial substitute.
• If rejection must capture arbitrary constraint and conversion errors while continuing, PL/pgSQL row-by-row exception logging is the closest functional approximation.
• For production ingestion pipelines, staging plus validation is often the preferred operational model.

One-line Takeaway

PostgreSQL cannot reproduce Oracle’s set-based, engine-native “log row errors and continue” behavior in a single statement; equivalent solutions require pre-validation, conflict handling, procedural exception capture, or staging-based ETL design.