PostgreSQL Connector

Back Next

PostgreSQL targets in mappings

To write data to PostgreSQL, configure a PostgreSQL object as the target in a mapping.

Specify the name and description of the PostgreSQL target. Configure the target and advanced properties for the target object.

The following table describes the target properties that you can configure in a Target transformation:

Property	Description
Connection	Name of the target connection, or create a connection parameter. If you want to overwrite the target connection properties at runtime, select the Allow parameter to be overridden at run time option. Specify the parameter file directory and name in the advanced session properties.
Target Type	Type of the target object. Select Single Object or Parameter.
Object	Name of the target object. You can select an existing target object from the displayed list or you can create a target at runtime. When you want to create a target at runtime, specify the target object name and the path for the target object. For a list of supported data types that you can write to PostgreSQL using the Create New at Runtime option, see the Data Type References chapter.
Parameter	A parameter file where you define values that you want to update without having to edit the task. Select an existing parameter for the target object or click New Parameter to define a new parameter for the target object. The Parameter property appears only if you select parameter as the target type. If you want to overwrite the target object at runtime, select the Allow parameter to be overridden at run time option. When the task runs, the Secure Agent uses the parameters from the file that you specify in the advanced session properties.
Operation	Type of the target operation. Select Insert, Upsert, Update, Delete, or Data Driven. To perform an upsert operation, you need to set the target advanced property Update Mode to Update Else Insert .
Data Driven Condition	Enables you to define expressions that flag rows for an insert, update, delete, or reject operation. Appears only when the operation type is Data Driven . When you configure the DD_REJECT operation in the data driven mode to reject data to PostgreSQL, the rejected records are not written to the error files and the session log shows the number of rejected rows as zero.
Update Columns	Specify the columns that you want to use as a logical primary key for performing update, upsert, and delete operations on the target. This field is not required if the target table already has a primary key. If the target table does not have a primary key, ensure that the columns selected in the Update Columns field has a unique constraint for the upsert operation. This property is not applicable for the insert operation.

The following table describes the PostgreSQL advanced target properties:

Property	Description
Update Mode	Specifies the mode to write data to PostgreSQL target. You can specify the following modes: Update As Update. Updates all rows flagged for update if the entries exist. Update Else Insert. Updates all rows flagged for update if the entries exist in the target. If the entries do not exist, the Secure Agent inserts the entries.
Override Target Query	An SQL statement to override the default update query that the Secure Agent generates for the update operation.
Schema Name	Overrides the schema name of the target object.
Target Table Name	Overrides the default PostgreSQL target table name.
Pre-SQL	The pre-SQL commands to run a query before you write data to PostgreSQL. You can partially parameterize pre-SQL with values specified in a parameter file.
Post-SQL	The post-SQL commands to run a query after you write data to PostgreSQL. You can partially parameterize post-SQL with values specified in a parameter file.
Truncate Target	The Secure Agent truncates the target before writing the data.
Enable target bulk load	Performs bulk upload when you configure an insert operation to write to PostgreSQL. Select this option to improve the performance of inserting data in bulk to PostgreSQL. Default is unselected. When you enable the target bulk mode to insert data to PostgreSQL, error files are not generated for rejected records.
Batch size	The number of rows that the Secure Agent writes in a single batch to PostgreSQL. Specify a batch size value that is greater than zero. Applicable if you select the Enable target bulk load option.
Reject File Directory	The directory that stores the rejected files. Specify the directory where you want to store the rejected files.
Reject File Name	Name of the rejected file that is stored in the reject file directory.
Forward Rejected Rows	Not applicable.