PostgreSQL Update

Executes a SQL update statement with the given properties.

Overview

This Snap executes a SQL update with the given properties. Keys from the document will be used as the columns to update and their values will be the updated value in the column.

Note: Updates will be batched up until the account's batch size property or until the structure of the update statement changes. An update statement will change if an incoming document contains different keys than a previous document.

Prerequisites

A valid PostgreSQL Account with the required permissions.

Support and Limitations

If you want to filter by vector values, you must query vector columns using String format in the Where clause.

Snap views

Type Description Examples of upstream and downstream Snaps
Input Each input document should contain the data for the columns to update and the values for the WHERE clause condition.
Output This Snap has no output views.
Learn more about Error handling.

Snap settings

Legend:
  • Expression icon (): Allows using JavaScript syntax to access SnapLogic Expressions to set field values dynamically (if enabled). If disabled, you can provide a static value. Learn more.
  • SnapGPT (): Generates SnapLogic Expressions based on natural language using SnapGPT. Learn more.
  • Suggestion icon (): Populates a list of values dynamically based on your Snap configuration. You can select only one attribute at a time using the icon. Type into the field if it supports a comma-separated list of values.
  • Upload : Uploads files. Learn more.
Learn more about the icons 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 - Update

Example: Update Employee Records
Schema name

String

 The database schema name. Selecting a schema filters the Table name list to show only those tables within the selected schema. The property is suggestible and will retrieve available database schemas during suggest values.
Note: The values can be passed using the pipeline parameters but not the upstream parameter.

Example: myschema

Default value: N/A
Table name

String

 Required. The name of the table to execute the update operation on.
Note: The values can be passed using the pipeline parameters but not the upstream parameter.

Example: people

Default value: N/A
Update condition

String/Expression

 Required. Specify the SQL WHERE clause of the update statement. You can define specific values or columns to update (Set condition) in the upstream Snap, such as Mapper Snap, and then use the WHERE clause to apply these conditions on the columns sourced from the upstream Snap.
Note: If the Update Condition field is left blank, the condition is applied on all the records of the target table.
Note: In certain scenarios where you want to use specific data from the upstream Snaps, and do not want to change that data in the Update Snap, then you need to place the data in the original structure of the input document. Hence, we recommend that you use the format for Update condition and input data format as shown below:
{
"valueToUpdate" : "true",
"original": {
"col1" : "KEY"
}
}
Tip: You can use primary key columns in the WHERE clause to identify specific records to update. For example, if id is the primary key, use id = $id to update a single record based on the incoming document's id value.
Note: In a scenario where the downstream Snap depends on the data processed in an Upstream Database Bulk Load Snap, use the Script Snap to add delay for the data to be available.

Examples:

Without using expressions

Using expressions

  • "EMPNO=$EMPNO and ENAME=$EMPNAME"
  • email = $email
  • emp=$emp
  • "emp='" + $emp + "'"
  • "EMPNO=" + $EMPNO + " and ENAME='" + $EMPNAME+ "'"
Warning: Using expressions that join strings together to create SQL queries or conditions has a potential SQL injection risk and is hence unsafe. Ensure that you understand all implications and risks involved before using concatenation of strings with '=' Expression enabled.

Default value: N/A
Number of retries

Integer/Expression

 Specifies the maximum number of attempts to be made to receive a response. The request is terminated if the attempts do not result in a response.

Example: 3

Default value: 0
Retry interval (seconds)

Integer/Expression

 Specifies the time interval between two successive retry requests. A retry happens only when the previous attempt resulted in an exception.

Example: 10

Default value: 1
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