PostgreSQL Bulk Upsert

Overview

This Snap enables you to perform bulk insert or update operations (using the MERGE command) into the existing tables or any input data stream. The upsert operation updates existing rows if the specified value exists in the target table and inserts a new row if the specified value does not exist in the target table.

This is a Write-type Snap.
Works in Ultra Tasks

Prerequisites

A valid account with the required permissions.

Snap views


Type	Description	Examples of upstream and downstream Snaps
Input	Requires the Upsert format and additional detail, if required, to insert new or update existing rows in bulk.	Mapper JSON Parser JSON Generator
Output	The output is in document view format. The output view lists the number of rows that were updated, modified, or inserted in the target table.	Mapper JSON Formatter
Learn more about Error handling.

Snap settings

Note: Learn about the common controls in the Snap settings dialog.


Field/Field set	Type	Description
Label `String`	Required. Specify a unique name for the Snap. Modify this to be more appropriate, especially if more than one of the same Snaps is in the pipeline. Default value: PostgreSQL Bulk Upsert Example: PostgreSQL Bulk Upsert
Schema name `String/Expression/ Suggestion`	The database schema name. Selecting a schema filters the Table name list to show only tables within that schema. Default value: N/A Example: emp_bulk_upsert_schema
Table name `String/Expression/ Suggestion`	Required. The table on which to execute the insert operation. Default value: N/A Example: emp_mdm_master
Key columns	Required. Specify the conditions to check for existing entries in the target table. Default value: N/A
Column `String/Expression/ Suggestion`	Required. Specify one or more columns from the target table to check for existing entries. Default value: N/A
Delete upsert condition `String/Expression`	Specify the delete condition to update, delete, or insert records. When the delete upsert condition is not blank and the condition is met, the records are deleted. Default value: N/A Example: emp_name="Charlie"
Header provided `Checkbox`	Select this checkbox if the header is included in the input schema. Note: HEADER, FORMAT, and ENCODING should not be provided as part of Additional COPY options. Default status: Deselected
Additional COPY options	Configure additional copy options, if any. Default value: N/A
COPY options `String/Expression`	Specify the COPY option. Default value: N/A Example: DELIMITER '\|', ESCAPE '`'
Snap Execution `Dropdown list`	Choose one of the three modes in which the Snap executes. Available options are: Validate & Execute: Performs limited execution of the Snap and generates a data preview during pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during pipeline runtime. Execute only: Performs full execution of the Snap during pipeline execution without generating preview data. Disabled: Disables the Snap and all Snaps that are downstream from it. Default value: Execute only Example: Validate & Execute

Troubleshooting


Error	Reason	Resolution
The Snap displays an error if you use an older version (below 15) of PostgreSQL.	An older version of PostgreSQL might exist. The MERGE command is available only from PostgreSQL version 15. Note: The MERGE command in PostgreSQL 15 version does not have a returning clause, which helps to output the number of records inserted/updated separately.	Use PostgreSQL DB version 15 to execute the MERGE command.
Key column name is required.	No key column(s) specified for checking for existing entries.	Please enter one or more key column names.
Key column name is not present in target table.	Incorrect key column(s) specified for checking for existing entries.	Please select one or more key column names from the suggestion box.
All columns in target table are key columns.	The merge will fail as all columns in the target table are key columns.	Please select one or more (but not all) key column names from the suggestion box.

Temporary files

During execution, data processing on Snaplex nodes occurs principally in-memory as streaming and is unencrypted. When processing larger datasets that exceed the available compute memory, the Snap writes unencrypted pipeline data to local storage to optimize the performance. These temporary files are deleted when the pipeline execution completes. You can configure the temporary data's location in the Global properties table of the Snaplex node properties, which can also help avoid pipeline errors because of the unavailability of space. Learn more about Temporary Folder in Configuration Options.