Support for PostgreSQL adds many new data types that are not supported in other databases, such as arrays, bit strings, geometric types, network address types, text search types, and range types. These new data types are not supported for Column Map Exits or Optim Relationships.

PostgreSQL does not require an array to declare its bounds, such as integer[]. The size of the array is unlimited. Even if an array declares a limit, the limit is not enforced, so any array has unlimited length. However, ODBC restricts the length based on the setting of the Max Varchar attribute in the data source configuration.

Note: During configuration of the ODBC data source for PostgreSQL, set the Max Varchar and Max LongVarChar attributes to the maximum number of characters in the text representation of your table columns. This information is required to ensure that the ODBC driver does not truncate data; it is also required to ensure that the Optim solution can prepare a row buffer of the appropriate size. During configuration, also set the Use Declare/Fetch option so that the ODBC driver does not attempt to cache the entire result set in memory, which might exhaust system resources.

PostgreSQL data types that are not supported by ODBC must be bound to VARCHAR, including the following data types:

• Array
• Bit String
• Geometric
• Interval
• JSON
• Money
• Network Address
• Range
• Text Search
• XML

The Max Varchar setting affects all of the above data types, not just VARCHAR. That setting also determines how much space the Optim solution reserves for the column in the row buffer if a maximum length cannot otherwise be determined, so it can affect the size of an extract file.

The following data types are treated as VARCHAR, and their space allocation is affected by the Max Varchar setting:

• Array (except bytea[] and text[])
• Geometric
• JSON
• Range
• Text Search

• bytea[]
• text[]

The maximum value the Optim solution supports for precision is 127. The maximum value it supports for scale is 126. When unspecified, the default precision and scale for the solution is (38,6).

The Optim solution allows you to extract from a table that has a numeric column defined outside the supported range, but any values that fall outside the supported range are rejected and not stored in the extract file.

PostgreSQL supports year values outside the range of traditional ANSI SQL (such as 4713 BC and 294276 AD), but the Optim solution does not properly return dates when the year is outside the ANSI SQL range of 0001 to 9999.

PostgreSQL allows time fractional precision to be unspecified. However, if fractional precision is unspecified, the Optim solution defaults to a fractional precision of 9.

PostgreSQL supports time zone offsets that contain seconds, but the Optim solution supports time zone offsets that contain only hours and/or minutes.

PostgreSQL allows bit varying precision to be unspecified (bit varying [ n ]).

If bit precision is unspecified, the Optim solution defaults to a bit precision of 64. Extracted values that fall outside the supported range are rejected and not stored in the extract file.

The following data types are treated as large objects (LOBs), so they will impact performance:

• bytea
• text
• xml

Some additional configuration might be required to Insert into an LOB column. It might be necessary to set the “bytea as LO” attribute in data source configuration.

Edit your .odbcinst.ini and .odbc.ini files to include various required attributes. These attributes include DriverUnicodeType, UseDeclare/Fetch, ByteaAsLongVarBinary, MaxVarcharSize, MaxLongVarcharSize, and other attributes.

Additional attributes might be required to meet your processing requirements. For more information on this subject, see the ODBC driver manual at https://odbc.postgresql.org/.

Here is an example of a .odbcinst.ini file for PostrgeSQL:

Here is an example of an .odbc.ini file for PostgreSQL:

It is important to set DriverUnicodeType=1 to tell the DataDirect driver manager that the PostgreSQL driver expects wide-char data in UTF-16. (By default, the DataDirect driver manager assumes UTF-8 and would not be able to interpret the information it receives.)

Also, note that in the above examples, Optim is installed to /home/optimusr, rather than the default /home/optimusr/IBM/InfoSphere. Hence the use of /home/optimusr as the root directory to get to the Data Direct ODBC driver for PostgreSQL.