User Guide : Map Connectors : Source and Target Map Connectors : PostgreSQL
 
Share this page             
PostgreSQL
PostgreSQL is an open-source object-relational database management system. It is derived from the original POSTGRES database management system, a research prototype. PostgreSQL retains the data model and data types of POSTGRES and replaces the PostQuel query language with an extended subset of SQL. The PostgreSQL Global Development Group website includes PostgreSQL documentation, FAQ, news, and a user forum.
Note:  You must have the PostgreSQL 64-bit client installed on the system and you must have access to PostgreSQL. If it is installed on your local PC, the drive and directory path of the PostgreSQL application must be included in your path. The path statement is automatically modified when your PostgreSQL client software is installed.
Other PostgreSQL Connectors
You can connect PostgreSQL interface in multimode to the target database. See PostgreSQL Multimode.
Unicode Support
PostgreSQL connectors include Unicode support for wide characters. To enable double-byte support, change the Regional and Language Options on your system to a double-byte language, such as Japanese.
Connectivity Pointers
The PostgreSQL client must be installed in your system to connect to PostgreSQL databases.
If the database has many tables, selecting the table from a list may take more time than typing the UserID.Tablename value in the source Table/View box. If you enter the table name without the user ID and then connect to the database, you can reselect the table in the source data browser to view it.
If possible, create query statements using the source database application, so that syntax and validity can be checked by the native application. You can then copy the query statement and paste it in the Query Statement box.
Connector-Specific Notes
The BINARY_FLOAT and BINARY_DOUBLE data types are not supported on Linux systems.
Limitations
No known limitations at this time.
Property Options
You can set the following source (S) and target (T) properties.
Property
S/T
Description
Encoding
ST
Sets the character encoding for XML source and target files.
For encoding choices, see Unicode Character Encodings.
WhereStmt
S
Provides a pass-through mechanism for SQL connectors where advanced users can construct the Where clause of the SQL query themselves. It can be used as an alternative to writing a lengthy query statement. You may use this to instruct the SQL database server to filter the data based on a particular condition before sending it to the integration platform. There is no default value for this property.
Note:  This property is not applicable when the source connection is a query statement or file. This property enables data filtering when you select a table.
SystemTables
ST
The SystemTables property is only applicable if the user is logged into the database as the Database Administrator (DBA). Only the DBA has access to SystemTables. If set to true, this property allows you to view all the tables created by the DBA. The system table names appear in the table list. The default value is false.
Views
ST
If set to True, this property allows you to see the view names in the table list along with the table names. Default is True.
Note:  This property supports only Append and DeleteAndAppend output modes and does not support the Replace output mode.
Synonyms
ST
If set to true, this property allows you to see synonyms. The alias names appear in the table list along with the tables. The default value is false.
CommitFrequency
T
Allows you to control how often data is committed to the database when the AutoCommit property is set to False.
The default value is zero that is, the data is committed at the end of the transformation, allowing rollback on error. This is the slowest setting. When performing large transformations, this is not practical as it may produce too many transaction log entries.
Specifying a nonzero value indicates that data is committed to the database after inserting or updating specified number of records.
ConstraintDDL
T
Allows you to specify additional SQL data definition language statements to be executed after their target table is created. This is similar to the support provided for SQL pass-through in the SQL import connectors. Each line must be a valid ODBC DDL statement. There is no default value for this property.
For example, you can have the statements:
CREATE UNIQUE INDEX index1 ON mytable (Field1 ASC)
CREATE INDEX index2 ON mytable (Field2, Field3)
These statements creates two indices on the table "mytable". The first statement does not allow duplicates and the index values are stored in ascending order. The second index is a compound index on fields Field2 and Field3.
The ConstraintDDL is executed only if the replace mode is used for the target. If there are any errors, the errors are written to the error and event log file. During the transformation, the transformation error dialog box appears. If you want to ignore the DDL errors, you may continue the transformation.
ConstraintDDL also supports an escaping mechanism that allows users to specify DDL in the native SQL of the DBMS. Any statement that is preceded by an "@" is sent directly to the DBMS.
The following statement is an Access DDL statement for creating a primary key for the table mytable:
@CREATE INDEX pk_mytable ON mytable (Field1, Field2) WITH PRIMARY
The Access ODBC driver does not support the extensions to SQL required to create a primary key with the ODBC variant of the SQL CREATE statement. The only way you can create the primary key for an Access table is with the native SQL.
This option works only in REPLACE mode.
Data types
The following data types are supported:
bigint
bit
bit varying
bytea
char
date
double precision
integer
numeric
oid
real
smallint
text
timestamp
varchar
Length
These are field lengths in your data. If you need to change field lengths, reset them in the schema.
Caution!  A field length shorter than the default may truncate data.