User Guide > Map Connectors > Source and Target Map Connectors > PostgreSQL Multimode
Was this helpful?
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.
You can quickly setup a multimode connector using the multimode wizard. For more information, see Setting Up Multimode Target Connector Using Multimode Wizard.
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.
Connector Parts
Connector parts are the fields you configure to connect with a data source or target.
The settings that are available depend on the connector you select.
For a list of all parts for target connectors, see Specifying Connector, Parts, and Properties.
Property Options
You can specify the following target properties:
Property Name
Description
Encoding
Sets the character encoding for XML source and target files.
For encoding choices, see Unicode 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 (Default) - 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.
Note:  SQL statements are sent to the SQL Log file only if the SQL Output property is set to either Target and SQL Log or SQL Log Only.
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 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
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.
Supported 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.
Last modified date: 02/09/2024