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 4.0 Character Encodings.
WhereStmt
S
Provides a pass-through mechanism for SQL connectors where advanced users can construct the Where clause of the SQL query. It can be used as an alternative to writing a lengthy query statement in the Query Statement text box. This may be used to get the SQL database server to filter the data based on a condition before it is sent to Map Designer. Exclude "WHERE" when you enter the clause. There is no default value for this property.
Do not apply the WhereStmt when the source connection is a Select statement. If you are using a Select statement, you should include the Where clause in that statement. The intent of this property is to enable data filtering when selecting 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 views. The view names appear in the table list along with the tables. The default value is true.
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
This allows you to control how often the data is committed to the database. By default, this value is zero. This means that data is committed at the end of the transformation, allowing rollback on error. This is the slowest setting. If you are performing large transformations, this is not practical, since it may fill up the transaction logs. If the CommitFrequency is set to a non-zero value, the connector does a database commit after the specified number of records have been inserted or updated in the table. This ensures the transaction log is not full but limits the restartability of the transformation.
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.