PostgreSQL Insert

Executes a SQL Insert statement using document keys as columns.

Overview

This Snap executes a SQL Insert statement using the document's keys as the columns to insert to and the document's values as the values to insert into the columns.

Prerequisites

A valid PostgreSQL Account with the required permissions.

Limitations

Works in Ultra Tasks if batching is disabled.

Known issues

  • If database metadata from an upstream Snap contains geography column data such as modifiers, those modifiers may not be written to the target table.

    Workaround: To write geographic data to the target table, create the table using the PostgreSQL - Execute Snap.

Snap views

Type Description Examples of upstream and downstream Snaps
Input This Snap has one input view by default that accepts the rows to insert into the table. A second input view can be added that accepts the table metadata document from a database Select Snap. The table metadata will then be used to guide the creation of the table in the database.
Output This Snap has at most one document output view.
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 - Insert

Example: Insert Records
Schema name

String

 Specify the database schema name. Selecting a schema filters the Table name list to show only those tables within the selected schema.
Note: The values can be passed using the pipeline parameters but not the upstream parameter.

Example: SYS

Default value: N/A
Table name

String

 Required. Specify the table that the rows will be inserted into.
Note: The values can be passed using the pipeline parameters but not the upstream parameter.
Note: You must check the datatype of each column in the table into which you want to insert the input data, and structure your data accordingly; else the endpoint generates an error. For example, if the column contact_id expects data in the UUID datatype, you must provide UUID data.

Example: users

Default value: N/A
Create table if not present

Checkbox

 Select this checkbox to automatically create the target table if it does not exist.

If a second input view is configured for the Snap and it contains a document with schema (metadata) from the source table, the Snap creates the new (target) table using the same schema (metadata). However, if the schema comes from a different database, the Snap might fail with the Unable to create table: "<table_name>" error due to data type incompatibility.

In the absence of a second input view, the Snap creates a table based on the data types of the columns generated from the first row of the input document (first input view).

Warning: Due to implementation details, a newly created table is not visible to subsequent database Snaps during runtime validation. If you want to immediately use the newly updated data you must use a child Pipeline that is invoked through a Pipeline Execute Snap.
CAUTION: If database metadata from an upstream Snap contains geography column data such as modifiers, those modifiers may not be written to the target table. For example, if the column metadata contains a polygon modifier, that modifier will not be written to the target table.

Workaround: Create the table using the PostgreSQL - Execute Snap.

Default value: Not selected
Preserve case sensitivity

Checkbox

 Select this checkbox to preserve the case sensitivity of the column names while performing the insert operation.

If you do not select this option, then Snap converts the column names in the input document to match the column names in the target database table before inserting the values. The Snap checks for the following three conditions:

  • If the column name in the table is in lower case, it modifies the column label to lower case.
  • If the column name in the table is in the upper case, it modifies the column label to upper case.
  • If the conditions mentioned above are not met, it takes the column label as is.

On the contrary, if you select this checkbox, the Snap does not convert the case of the column names and inserts the names as-is in the target database. Therefore, ensure that the column labels in the input document match the column labels in the target database table. Else, the Snap does not insert the values and displays an error.

Default value: Not selected
Number of retries

Integer/Expression

 Specify 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.

Default value: 0

Example: 3
Retry interval (seconds)

Integer/Expression

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

Default value: 1

Example: 10
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