Input 

Document

• Min: 1

• Max: 1

• Mapper

• JSON Parser

• JSON Generator

Requires the Upsert format and additional detail, if required, to insert new or update existing rows in bulk.

Output

Document

• Min: 0

• Max: 1

• Mapper

The output is in document view format. The data from the incoming document loaded into the destination table is the output of this Snap. It gives the load statistics after the operation is completed.

The output view also lists the number of rows that were updated, modified, or inserted in the target table.

Error

Error handling is a generic way to handle errors without losing data or failing the Snap execution. You can handle the errors that the Snap might encounter when running the pipeline by choosing one of the following options from the When errors occur list under the Views tab:

• Stop Pipeline Execution: Stops the current pipeline execution if the Snap encounters an error.

• Discard Error Data and Continue: Ignores the error, discards that record, and continues with the remaining records.

• Route Error Data to Error View: Routes the error data to an error view without stopping the Snap execution.

Learn more about Error handling in Pipelines.

Label*

String

The name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your 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: None.
Example: emp_bulk_upsert_schema

Table name

String/Expression/Suggestion

The table on which to execute the insert operation.

Default value: None.
Example: emp_mdm_master

Key columns*

Specify the conditions to check for existing entries in the target table.

Column*

String/Expression/Suggestion

Specify one or more columns from the target table to check for existing entries.

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.

Example: emp_name="Charlie"

Header provided

Checkbox

Select this checkbox if the header is included in the input schema.

Additional COPY options

Configure additional copy options, if any.

COPY options

String/Expression

Specify the COPY option.

Default value: None.
Example: DELIMITER '|', ESCAPE '`'

Snap Execution

Dropdown list

Select one of the following three modes in which the Snap executes:

• Validate & Execute: Performs limited execution of the Snap based on Preview Document Count value in User settings 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

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.

Use PostgreSQL DB version 15 to execute the MERGE command.

The MERGE command in PostgreSQL 15 version does not have a returning clause, which helps to output the number of records inserted/updated separately.

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.