PostgreSQL Merge Into Equivalent for Conditional Updates

PostgreSQL Merge Into Equivalent for Conditional Updates

The article demonstrates a PostgreSQL technique to emulate MERGE INTO logic using an UPDATE statement with a subquery. The example updates invoice records by joining with a FIND_ENTITY table to set shipping location IDs.

Why This Matters

PostgreSQL lacks a native MERGE INTO statement, but this pattern allows conditional updates without full table scans. However, improper indexing on INVOICE_M_ID or large datasets could lead to performance degradation, as the subquery executes for each matching row.

Key Insights

• “Subquery-based updates avoid full table scans when joining with source data.”
• “FIND_ENTITY table joins provide dynamic reference data for invoice updates.”
• “Temporal used by Stripe, Coinbase” – Not applicable; example focuses on PostgreSQL-specific logic.

Working Example

UPDATE PSMT_INVOICE_M
SET SHIPPING_COUNTRY_ID = SRC.COUNTRY_ID,
    SHIPPING_CITY_ID = SRC.CITY_ID,
    SHIPPING_TOWN_ID = SRC.TOWN_ID
FROM (
    SELECT
        PM.INVOICE_M_ID,
        FE.COUNTRY_ID,
        FE.CITY_ID,
        FE.TOWN_ID
    FROM PSMT_INVOICE_M PM
    JOIN FIND_ENTITY FE ON PM.ENTITY_ID = FE.ENTITY_ID
) SRC
WHERE PSMT_INVOICE_M.INVOICE_M_ID = SRC.INVOICE_M_ID;

Practical Applications

• Use Case: Invoice system updates shipping addresses via entity lookup tables.
• Pitfall: Overly complex subqueries can lead to performance degradation in large datasets.

References:

• https://dev.to/fazliakca/postgresql-merge-into-35o0

---