User Guide : Map Connectors : Source and Target Map Connectors : PostgreSQL Multimode
 
Share this page                  
PostgreSQL Multimode
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.
This multimode connector specifies field width in characters, which means the width of a field is the specified number of characters. To determine the number of bytes of the field, both the encoding and the particular characters must be examined. For example, see the following notes:
• In UTF-8, a single character may be encoded into one, two, three, four, or five bytes. Thus a five-character field is written as at least five bytes and at most 25 bytes.
• UCS-2 is a double-byte character set; characters always take two bytes and UCS-2 can only represent the first Unicode plane.
• UTF-16 represents most existing characters as two bytes. However, characters that do not appear in the first Unicode plane take up four bytes. Currently UTF-16 is treated as UCS-2.
• Shift-JIS is a multibyte character set. In Shift-JIS, a character takes either 1 or 2 bytes, depending on the character. Thus, a five-character wide field takes from 5 to 10 bytes.
The main distinction between single and multimode connectors is in the use of event actions:
• Single mode connectors - Use event actions to access one target file or table at a time in a transformation.
• Multimode connectors - Use multimode event actions to access multiple target tables in the same transformation, each time the transformation runs.
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
DateValMask Function: Map Designer does not support milliseconds in DateValMask date conversions. Although a mask is provided for them, Map Designer always returns zeros in those places.
Property Options
You can set the following source (S) and target (T) properties.
Property Name
Description
Encoding
Sets the character encoding for XML source and target files.
For encoding choices, see Unicode 4.0 Character Encodings.
Shift-JIS encoding is used only in Japanese operating systems.
SQL Output
Allows you to select bound or unbound mode and whether you want to write SQL statements to a SQL log or not. The bound mode is faster, since bind variables are used. The options available are:
• Target Only - Uses bound mode that uses bind variables. SQL statements are sent to the target and not to the SQL log specified in the SQL Log property. This is the default value.
• Target Only (Unbound mode) - Uses unbound mode that does not use bind variables. The literal SQL statement is sent to the database engine. SQL statements are sent to the target and not to the SQL log specified in the SQL Log property.
• Target and SQL Log - Sends SQL statements to the target and to the SQL log specified in the SQL Log property.
• SQL Log Only - Sends SQL statements only to the SQL log file specified in the SQL Log property.
SQL Log
Specify the SQL log file name. By default, it is sql.log in the default installation directory. To specify a different log file name, click once in the box and then click the search icon. Browse to the appropriate location and the file.
UpdateNullFields
If set to true, null values are sent to the database when inserting or updating records. The default value is true.
If you select false:
• Null values are not sent to the database when you insert or update records.
• Forces the connector to operate in unbound mode, which may cause slower performance.
Best Practice — If fields in the target record are not mapped, then the null values are passed to the target. If you do not want to write to these fields, then it is recommended to set the value for UpdateNullFields to False.
SystemTables
This property is only applicable if the user is logged in to the database as the Database Administrator (DBA).
If set to true, this property allows you to see all the tables created by the DBA. The system table names appear in the table list. The default is false.
Views
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
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.
Datatypes
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.