PostgreSQL Bulk Upsert with Conditional Delete, Insert, and Update

This example pipeline demonstrates a complete database workflow that creates a PostgreSQL table, populates it with initial data, performs bulk upsert operations using CSV data, and retrieves the final results. The pipeline showcases database table management, data insertion, conditional updates, and data retrieval operations in a sequential manner.


PostgreSQL Bulk Upsert Example Pipeline

Download this pipeline.

  1. Configure the PostgreSQL - Multi Execute Snap to execute multiple SQL statements.

    This includes dropping any existing target table, creating a new table with columns for id, name, quantity, and price, and inserting initial sample data records: Apple, Banana, and Grapesrecords.


    PostgreSQL Multi Execute Snap configuration

  2. Configure the CSV Generator Snap to generate CSV content containing product data with updated quantities and prices for both existing and new items.
  3. Configure the PostgreSQL Bulk UpsertSnap to perform bulk upsert operations on thetarget_table in the public schema. Configure the Delete upsert condition also.
    PostgreSQL Bulk Upsert Snap configuration
  4. Validate the pipeline to perform bulk upsert operations.

    The Snap performs bulk upsert operations on the target_table using the CSV data, with id as key column, and includes a conditional delete operation that removes records where id equals 5 and name equals Grapes. The output displays the details of the updated, inserted, and deleted records.


    Bulk Upsert output showing updated, inserted, and deleted records

  5. Verify the results using PostgreSQL - Select Snap.

    After validation, all records from the target table are updated, and the data can be further used for other downstream operations.


    PostgreSQL Select Snap output showing final table data