PostgreSQL Connector

Back Next

PostgreSQL sources in mappings

In a mapping, you can configure a Source transformation to represent a PostgreSQL source.

The following table describes the PostgreSQL source properties that you can configure in a Source transformation:

Property	Description
Connection	Name of the source 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.
Source type	Type of the source object. Select Single Object, Multiple Objects, Query, or Parameter. When you select multiple objects as the source type to read from multiple PostgreSQL sources, you can use the advanced relationship option to define the relationship for the objects that you want to join. When you select query as the source type, specify the SQL statement in the Query field. You can partially parameterize the query source type. If you want to overwrite the query 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.
Object	Name of the source object.
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 source object or click New Parameter to define a new parameter for the source object. The Parameter property appears only if you select parameter as the source type. If you want to overwrite the source 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.

The following table describes the PostgreSQL query options that you can configure in a Source transformation:

Property	Description
Filter	Filter value in a read operation. Click Configure to add conditions to filter records and reduce the number of rows that the Secure Agent reads from the source. You can specify the following filter conditions: Not parameterized . Use a basic filter to specify the object, field, operator, and value to select specific records. Completely parameterized . Use a parameter to represent the field mapping. Advanced . Use an advanced filter to define a more complex filter condition.
Sort	Add conditions to sort records. You can specify the following sort conditions: Not parameterized. Select the fields and type of sorting to use. Parameterized. Use a parameter to specify the sort option. Sort Order. Sorts data in ascending or descending order, according to a specified sort condition.

Property

Description

Filter

Filter value in a read operation. Click

Configure

to add conditions to filter records and reduce the number of rows that the Secure Agent reads from the source.

You can specify the following filter conditions:

Not parameterized
. Use a basic filter to specify the object, field, operator, and value to select specific records.

Completely parameterized
. Use a parameter to represent the field mapping.

Advanced
. Use an advanced filter to define a more complex filter condition.

Sort

Add conditions to sort records.

You can specify the following sort conditions:

Not parameterized.
Select the fields and type of sorting to use.

Parameterized.
Use a parameter to specify the sort option.

Sort Order.
Sorts data in ascending or descending order, according to a specified sort condition.

The following table describes the PostgreSQL advanced source properties that you can configure in a Source transformation:

Property	Description
Pre-SQL	The pre-SQL commands to run a query before you read data from 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 a target. You can partially parameterize post-SQL with values specified in a parameter file.
Fetch Size	Determines the number of rows to read in one resultant set from PostgreSQL. Specifying a number limits the number of rows to fetch with each trip to the database and avoids unnecessary memory consumption. You can specify a maximum fetch size of 2147483647. Default is 100000.
Schema Name	Overrides the schema name of the source object.
Source Table Name	Overrides the default PostgreSQL source table name.
Tracing Level	Sets the amount of details that appear in the log file. You can choose terse, normal, verbose initialization, or verbose data. Default is normal.
SQL Override	The SQL statement to override the default query generated from the specified source type to read data from the PostgreSQL source. You can partially parameterize SQL override with values specified in a parameter file. Ensure that the list of selected columns, data types, and the order of the columns that appear in the query matches the columns, data types, and order in which they appear in the source object. SQL override is not applicable when you enable partitioning. If you specify an SQL override and configure partitioning, the mapping fails.