ODI Adaptation Guide For PostgreSQL 8.x.x, 9.x.x

Install PostgreSQL and create a database - database configuration/design tasks

If you are tasked with installing and configuring the database, here is a list of steps to be taken:

Compile and install the PostgreSQL Server on your computer. PostgreSQL is a free database, you can download the sources from www.postgresql.org.

Read PostgreSQL installation notes for details about the data directory creation with the initdb utility.

Set configuration parameters in postgresql.conf:

For PostgreSQL 8.1 and 8.2 only: UPDATE / DELETE WHERE CURRENT OF needs oid column support. Starting with PostgreSQL version 8.1, user tables do not get the oid column by default. You must set the default_with_oid configuration parameter to "on" in order to get oid columns created.

Starting with PostgreSQL 8.3 and the dbmpgs83x, WHERE CURRENT OF is supported by the server, and no longer requires setting the default_with_oid parameter. However, setting this parameter is still necessary if you plan to use ROWID keywords in SQL, as these can be converted to oid keywords.

Note that PostgreSQL 9.1 and higher have by default the standard_conforming_strings parameter set to on. Thus the ODI drivers >= 9.1 do no longer escape the backslash characters in string literals with a second backslash.

Start a postmaster process to listen to database client connections.
Important: If you want to connect through TCP (for example from a Windows PostgreSQL client), you must start postmaster with the -i option and setup the pg_hba.conf file for security (trusted hosts and users).

Create a PostgreSQL database with the createdb utility:
   $ createdb -h hostname dbname

If you plan to use SERIAL emulation, you need the plpgsql procedure language, because the database interface uses this language to create serial triggers. Starting with PostgreSQL version 9.0, the plpgsql language is available by default. Prior to version 9.0, you must create the language in your database with the following command:
   $ createlang -h hostname plpgsql dbname

Connect to the database as the administrator user and create a database user dedicated to your application, the application administrator:
   dbname=# CREATE USER appadmin PASSWORD 'password';
   CREATE USER
   dbname=# GRANT ALL PRIVILEGES ON DATABASE dbname TO appadmin;
   GRANT
   dbname=# \q

Create the application tables. Do not forget to convert INFORMIX data types to PostgreSQL data types. See issue Data Type Conversion Table for more details.

If you plan to use the SERIAL emulation, you must prepare the database. See issue Serial Data Types for more details.

Prepare the runtime environment - connecting to the database

In order to connect to PostgreSQL, you must have a PostgreSQL database driver "dbmpgs*" in FGLDIR/dbdrivers. On HP/UX LP64, the PostgreSQL database driver must be linked with the libxnet library if you want to use networking.

The PostgreSQL client software is required to connect to a database server. Check whether the PostgreSQL client library (libpq.*) is installed on the machine where the 4gl programs run.

Make sure that the PostgreSQL client environment variables are properly set. Check, for example, PGDIR (the path to the installation directory), PGDATA (the path to the data files directory), etc. See the PostgreSQL documentation for more details.

Verify the environment variable defining the search path for database client shared libraries (libpq.so on UNIX, LIBPQ.DLL on Windows). On UNIX platforms, the variable is specific to the operating system. For example, on Solaris and Linux systems, it is LD_LIBRARY_PATH, on AIX it is LIBPATH, or HP/UX it is SHLIB_PATH. On Windows, you define the DLL search path in the PATH environment variable.

PostgreSQL version

Shared library environment setting

PostgreSQL 8.0 and higher
UNIX: Add $PGDIR/lib to LD_LIBRARY_PATH (or its equivalent).
Windows: Add %PGDIR%\bin to PATH.

To verify if the PostgreSQL client environment is correct, you can start the PostgreSQL command interpreter:

$ psql dbname -U appadmin -W

Set up the fglprofile entries for database connections.

Note: Make sure that you are using the ODI driver corresponding to the database client and server version. Because Informix features emulation are dependant from the database server version, it is mandatory to use the same version of the database client and ODI driver as the server version.

DATE and DATETIME data types

INFORMIX provides two data types to store dates and time information:

DATE = for year, month and day storage.
DATETIME = for year to fraction(1-5) storage.

PostgreSQL provides the following data type to store date and time information:

DATE = for year, month, day storage.
TIME [(p)] [{with|without} time zone] = for hour, minute, second and fraction storage.
TIMESTAMP [(p)] [{with|without} time zone] = for year, month, day, hour, minute, second, fraction storage.

String representing date time information:

INFORMIX is able to convert quoted strings to DATE / DATETIME data if the string contents matches environment parameters (i.e. DBDATE, GL_DATETIME). As in INFORMIX, PostgreSQL can convert quoted strings to date time data according to the DateStyle session parameter. PostgreSQL always accepts ISO date time strings.

Date arithmetic:

INFORMIX supports date arithmetic on DATE and DATETIME values. The result of an arithmetic expression involving dates/times is a number of days when only DATEs are used, and an INTERVAL value if a DATETIME is used in the expression.
In PostgreSQL, the result of an arithmetic expression involving DATE values is an INTEGER representing a number of days.
INFORMIX automatically converts an integer to a date when the integer is used to set a value of a date column. PostgreSQL does not support this automatic conversion.
Complex DATETIME expressions ( involving INTERVAL values for example) are INFORMIX-specific and have no equivalent in PostgreSQL.

Solution:

PostgreSQL has the same DATE data type as INFORMIX ( year, month, day ). So you can use PostgreSQL DATE data type for INFORMIX DATE columns.

PostgreSQL TIME(0) WITHOUT TIME ZONE data type can be used to store INFORMIX DATETIME HOUR TO SECOND values. The database interface makes the conversion automatically.

INFORMIX DATETIME values with any precision from YEAR to FRACTION(n) can be stored in PostgreSQL TIMESTAMP(n+1) WITHOUT TIME ZONE columns. The database interface makes the conversion automatically. Missing date or time parts default to 1900-01-01 00:00:00.0. For example, when using a DATETIME HOUR TO MINUTE with the value of "11:45", the PostgreSQL TIMESTAMP value will be "1900-01-01 11:45:00.0".

Note:

SQL Statements using expressions with TODAY / CURRENT / EXTEND must be reviewed and adapted to the native syntax.
Literal DATETIME and INTERVAL expressions (i.e. DATETIME ( 1999-10-12) YEAR TO DAY) are not converted.

Reserved words

SQL object names like table and column names cannot be SQL reserved words in PostgreSQL.

Solution:

Table or column names which are PostgreSQL reserved words must be renamed.

ROWIDs

When creating a table, INFORMIX automatically adds a ROWID integer column (applies to non-fragmented tables only). The ROWID column is auto-filled with a unique number and can be used like a primary key to access a given row.

When the feature is enabled, PostgreSQL tables are automatically created with a OID column (Object Identifier) of type INTEGER. The behavior is equivalent to INFORMIX ROWID columns (see Solution).

Solution:

The database automatically converts ROWID keywords to OID for PostgreSQL. You can execute "SELECT ROWID FROM" and "UPDATE .. WHERE ROWID = ?" statements as with INFORMIX.

Notes:

Starting with PostgreSQL version 8.1, OIDs are no longer supported by default. You need to define the default_with_oid parameter in postgresql.conf to get OID columns created for tables. See Database configuration and design tasks.
SQLCA.SQLERRD[6]is not supported. All references to SQLCA.SQLERRD[6] must be removed because this variable will not hold the ROWID of the last INSERTed or UPDATEd row when using the PostgreSQL interface.

SERIAL data types

INFORMIX supports the SERIAL, SERIAL8 and BIGSERIAL data types to produce automatic integer sequences. SERIAL is based on INTEGER (32 bit), while SERIAL8 and BIGSERIAL can store 64 bit integers:

The table column must be of type SERIAL, SERIAL8 or BIGSERIAL.
To generate a new serial, no value or a zero value is specified in the INSERT statement:
INSERT INTO tab1 ( c ) VALUES ( 'aa' )
INSERT INTO tab1 ( k, c ) VALUES ( 0, 'aa' )
After INSERT, the new SERIAL value is provided in SQLCA.SQLERRD[2], while the new SERIAL8 and BIGSERIAL value must be fetched with a SELECT dbinfo('bigserial') query.

INFORMIX allows you to insert rows with a value different from zero for a serial column. Using an explicit value will automatically increment the internal serial counter, to avoid conflicts with future INSERT statements that are using a zero value :
    CREATE TABLE tab ( k SERIAL ); --> internal counter = 0
    INSERT INTO tab VALUES ( 0 );   --> internal counter = 1
    INSERT INTO tab VALUES ( 10 ); --> internal counter = 10
    INSERT INTO tab VALUES ( 0 );   --> internal counter = 11
    DELETE FROM tab;              --> internal counter = 11
    INSERT INTO tab VALUES ( 0 );   --> internal counter = 12

PostgreSQL SERIAL data type:

PostgreSQL's SERIAL data type has the same name as in INFORMIX, but it behaves differently.
You cannot define a start value ( SERIAL(100) ).
You cannot specify zero as serial value to get a new serial, the PostgreSQL serial is based on default values, thus you must omit the serial column in the INSERT statement.
When you INSERT a row with a specific value for the serial column, the underlying sequence will not be incremented. As result, the next INSERT that does not specify the serial column may get a new sequence that was already inserted explicitly.
With some old versions of PostgreSQL, when you drop the table you must drop the sequence too.

PostgreSQL sequences:

Sequences are totally detached from tables.
The purpose of sequences is to provide unique integer numbers.
Sequences are identified by a sequence name.
To create a sequence, you must use the CREATE SEQUENCE statement.
Once a sequence is created, it is permanent (like a table).
To get a new sequence value, you must use the nextval function:
INSERT INTO tab1 VALUES ( nextval('tab1_seq'), ... )
To get the last generated number, PostgreSQL provides the currval function :
SELECT currval('tab1_seq')

Solution:

The INFORMIX SERIAL data type can be emulated with three different methods, based on the following FGLPROFILE setting:

dbi.database.<dbname>.ifxemul.datatype.serial.emulation

This entry can have the following values: "native", "regtable" and "trigseq".

1.Using the native serial emulation

The "native" mode is the default serial emulation mode, using the native PostgreSQL SERIAL data type. In this mode, the original type name will be left untouched by the SQL Translator and you will get the behavior of the PostgreSQL SERIAL column type, based on sequences.

Note: INSERT statements cannot use the serial column, even with a value zero. When using a NULL value, PostgreSQL will report an non-null constraint error. Therefore, the serial column must be omitted from the INSERT statement.

The sqlca.sqlerrd[2] register is not set after an INSERT when using a PostgreSQL version prior to 8.3.

See also the PostgreSQL documentation for more details about the native SERIAL type.

2. Using the regtable serial emulation

With the "regtable" mode, the SERIAL data type is emulated with a PostgreSQL INTEGER data type and INSERT triggers using the table SERIALREG which is dedicated to sequence production. After an insert, sqlca.sqlerrd[2] register holds the last generated serial value. BIGSERIAL and SERIAL8 types can be converted to BIGINT in PostgreSQL, but the sqlca.sqlerrd[2] register cannot be used since it is defined as an INTEGER type.

The triggers can be created manually during the application database installation procedure, or automatically from a BDL program: When a BDL program executes a CREATE [TEMP] TABLE with a SERIAL column, the database interface automatically converts the SERIAL data type to INTEGER and dynamically creates the triggers.

You must create the SERIALREG table as follows:

CREATE TABLE SERIALREG (
     TABLENAME VARCHAR(50) NOT NULL,
     LASTSERIAL DECIMAL(20,0) NOT NULL,
     PRIMARY KEY ( TABLENAME )
)

Warning: The SERIALREG table must be created before the triggers. The serial production is based on the SERIALREG table which registers the last generated number for each table. If you delete rows of this table, sequences will restart at 1 and you will get unexpected data.

In database creation scripts, all SERIAL[(n)] data types must be converted to INTEGER data types and you must create one trigger for each table. To know how to write those triggers, you can create a small Genero program that creates a table with a SERIAL column. Set the FGLSQLDEBUG environment variable and run the program. The debug output will show you the native trigger creation command.

Check if your application uses tables with a SERIAL column that can contain a NULL value. Consider removing the serial column from the INSERT statements.

Using the trigseq serial emulation

With "trigseq", the SERIAL data type is emulated with a PostgreSQL INTEGER data type and INSERT triggers using a sequence <tablename>_seq. After an insert, sqlca.sqlerrd[2] register holds the last generated serial value.

With this emulation mode, INSERT statements using NULL for the SERIAL column will produce a new serial value:
INSERT INTO tab (col1,col2) VALUES (NULL,'data')
This behavior is mandatory in order to support INSERT statements which do not use the serial column:
INSERT INTO tab (col2) VALUES ('data')
Check if your application uses tables with a SERIAL column that can contain a NULL value.Consider removing the serial column from the INSERT statements.

Issues common to all serial emulation modes

Since sqlca.sqlerrd[2] is defined as an INTEGER, it cannot hold values from BIGSERIAL (BIGINT) auto incremented columns. If you are using BIGSERIAL columns, you must query the sequence pseudo-column CURRVAL() or fetch the LASTSERIAL column from the SERIALREG table if used.

For SQL portability, INSERT statements should be reviewed to remove the SERIAL column from the list.
For example, the following statement:
   INSERT INTO tab (col1,col2) VALUES (0, p_value)
can be converted to :
   INSERT INTO tab (col2) VALUES (p_value)
Static SQL INSERT using records defined from the schema file must also be reviewed :
   DEFINE rec LIKE tab.*
   INSERT INTO tab VALUES ( rec.* )   -- will use the serial column
can be converted to :
   INSERT INTO tab VALUES rec.* -- without braces, serial column is removed

Important: When using the Static SQL INSERT and UPDATE syntax using record.* without braces, make sure that you database schema files contain information about serials: This information can be lost when extracting the schema from a PostgreSQL database which does not use native serial emulation. See Database Schema for more details about the serial flag in column type encoding (data type code must be 6)

Outer joins

In INFORMIX SQL, outer tables can be defined in the FROM clause with the OUTER keyword:

SELECT ... FROM a, OUTER(b)
 WHERE a.key = b.akey

SELECT ... FROM a, OUTER(b,OUTER(c))
 WHERE a.key = b.akey
   AND b.key1 = c.bkey1
   AND b.key2 = c.bkey2

PostgreSQL supports the ANSI outer join syntax:

SELECT ... FROM cust LEFT OUTER JOIN order
                     ON cust.key = order.custno

SELECT ...
  FROM cust LEFT OUTER JOIN order
                 LEFT OUTER JOIN item
                 ON order.key = item.ordno
            ON cust.key = order.custno
 WHERE order.cdate > current date

See the PostgreSQL reference for a complete description of the syntax.

Solution:

For better SQL portability, use the ANSI outer join syntax instead of the old Informix OUTER syntax.

The PostgreSQL interface can convert most INFORMIX OUTER specifications to ANSI outer joins.

Prerequisites:

In the FROM clause, the main table must be the first item and the outer tables must be listed from left to right in the order of outer levels.
Example which does not work : "FROM OUTER(tab2), tab1".
The outer join in the WHERE part must use the table name as prefix.
Example : "WHERE tab1.col1 = tab2.col2".

Restrictions:

Additional conditions on outer table columns cannot be detected and therefore are not supported:
Example : "... FROM tab1, OUTER(tab2) WHERE tab1.col1 = tab2.col2 AND tab2.colx > 10".
Statements composed of 2 or more SELECT instructions using OUTERs are not supported.
Example : "SELECT ... UNION SELECT" or "SELECT ... WHERE col IN (SELECT...)"

Remarks:

Table aliases are detected in OUTER expressions.
OUTER example with table alias : "OUTER( tab1 alias1)".
In the outer join, <outer table>.<col> can be placed on both right or left sides of the equal sign.
OUTER join example with table on the left : "WHERE outertab.col1 = maintab.col2 ".
Table names detection is not case-sensitive.
Example : "SELECT ... FROM tab1, TAB2 WHERE tab1.col1 = tab2.col2".
Temporary tables are supported in OUTER specifications.

Database concepts

Most BDL applications use only one database entity (in the meaning of INFORMIX). But the same BDL application can connect to different occurrences of the same database schema, allowing several users to connect to those different databases.

Like INFORMIX servers, PostgreSQL can handle multiple database entities. Tables created by a user can be accessed without the owner prefix by other users as long as they have access privileges to these tables.

Solution:

Create a PostgreSQL database for each INFORMIX database.

Data consistency and concurrency management

Data consistency involves readers that want to access data currently modified by writers, and concurrency data access involves several writers accessing the same data for modification. Locking granularity defines the amount of data concerned when a lock is set (row, page, table, ...).

INFORMIX

INFORMIX uses a locking mechanism to handle data consistency and concurrency. When a process changes database information with UPDATE, INSERT or DELETE, an exclusive lock is set on the touched rows. The lock remains active until the end of the transaction. Statements performed outside a transaction are treated as a transaction containing a single operation and therefore release the locks immediately after execution. SELECT statements can set shared locks according to the isolation level. In case of locking conflicts (for example, when two processes want to acquire an exclusive lock on the same row for modification or when a writer is trying to modify data protected by a shared lock), the behavior of a process can be changed by setting the lock wait mode.

Control:

Lock wait mode : SET LOCK MODE TO ...
Isolation level : SET ISOLATION TO ...
Locking granularity : CREATE TABLE ... LOCK MODE {PAGE|ROW}
Explicit exclusive lock : SELECT ... FOR UPDATE

Defaults:

The default isolation level is read committed.
The default lock wait mode is "not wait".
The default locking granularity is per page.

PostgreSQL

When data is modified, exclusive locks are set and held until the end of the transaction. For data consistency, PostgreSQL uses a multi-version consistency model: A copy of the original row is kept for readers before performing writer modifications. Readers do not have to wait for writers as in INFORMIX. The simplest way to think of the PostgreSQL implementation of read consistency is to imagine each user operating a private copy of the database, hence the multi-version consistency model. The lock wait mode cannot be changed as in INFORMIX. Locks are set at the row level in PostgreSQL and this cannot be changed.

Control:

No lock wait mode control is provided.
Isolation level : SET TRANSACTION ISOLATION LEVEL ...
Explicit exclusive lock : SELECT ... FOR UPDATE

Defaults:

The default isolation level is Read Committed.

The main difference between INFORMIX and PostgreSQL is that readers do not have to wait for writers in PostgreSQL.

Solution:

The SET ISOLATION TO ... INFORMIX syntax is replaced by SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL ... in PostgreSQL. The next table shows the isolation level mappings done by the PostgreSQL database driver:

SET ISOLATION instruction in program	Native SQL command
SET ISOLATION TO DIRTY READ	SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED
SET ISOLATION TO COMMITTED READ [READ COMMITTED] [RETAIN UPDATE LOCKS]	SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED
SET ISOLATION TO CURSOR STABILITY	SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED
SET ISOLATION TO REPEATABLE READ	SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE

For portability, it is recommended that you work with INFORMIX in the read committed isolation level, make processes wait for each other (lock mode wait), and create tables with the "lock mode row" option.

See the INFORMIX and PostgreSQL documentation for more details about data consistency, concurrency and locking mechanisms.

SELECT FOR UPDATE

A lot of BDL programs use pessimistic locking in order to avoid several users editing the same rows at the same time.

   DECLARE cc CURSOR FOR
         SELECT ... FROM tab WHERE ... FOR UPDATE
   OPEN cc
   FETCH cc <-- lock is acquired
   ...
   CLOSE cc <-- lock is released

In both INFORMIX and PostgreSQL, locks are released when closing the cursor or when the transaction ends.

PostgreSQL locking granularity is at the row level.

To control the behavior of the program when locking rows, INFORMIX provides a specific instruction to set the wait mode:

SET LOCK MODE TO { WAIT | NOT WAIT | WAIT seconds }

The default mode is NOT WAIT. This as an INFORMIX-specific SQL statement; PostgreSQL has no equivalent for "SET LOCK MODE TO NOT WAIT".

Solution:

The database interface is based on an emulation of an INFORMIX engine using transaction logging. Therefore, opening a SELECT ... FOR UPDATE cursor declared outside a transaction will raise an SQL error -255 (not in transaction).

You must review the program logic if you use pessimistic locking because it is based on the NOT WAIT mode which is not supported by PostgreSQL.

Transactions handling

INFORMIX and PostgreSQL handle transactions in a similar manner.

INFORMIX native mode (non ANSI):

Transactions are started with BEGIN WORK.
Transactions are validated with COMMIT WORK.
Transactions are canceled with ROLLBACK WORK.
Savepoints can be set with SAVEPOINT name [UNIQUE].
Transactions can be rolled back to a savepoint with ROLLBACK [WORK] TO SAVEPOINT [name].
Savepoints can be released with RELEASE SAVEPOINT name.
Statements executed outside of a transaction are automatically committed.
DDL statements can be executed (and canceled) in transactions.

PostgreSQL supports transaction with savepoints:

Transactions are started with BEGIN WORK.
Transactions are validated with COMMIT WORK.
Transactions are canceled with ROLLBACK WORK.
Savepoints can be placed with SAVEPOINT name.
Transactions can be rolled back to a savepoint with ROLLBACK TO SAVEPOINT name.
Savepoints can be released with RELEASE SAVEPOINT name.
Statements executed outside of a transaction are automatically committed.
DDL statements can be executed (and canceled) in transactions.
If an SQL error occurs in a transaction, the whole transaction is aborted.

Transactions in stored procedures: avoid using transactions in stored procedures to allow the client applications to handle transactions, according to the transaction model.

The main difference between INFORMIX and PostgreSQL resides in the fact that PostgreSQL cancels the entire transaction if an SQL error occurs in one of the statements executed inside the transaction. The following code example illustrates this difference:

   CREATE TABLE tab1 ( k INT PRIMARY KEY, c CHAR(10) )
   WHENEVER ERROR CONTINUE
   BEGIN WORK
   INSERT INTO tab1 ( 1, 'abc' )
   INSERT INTO tab1 ( 1, 'abc' ) -- PK constraint violation = SQL Error, whole TX aborted
   COMMIT WORK

With INFORMIX, this code will leave the table with one row inside, since the first INSERT statement succeeded. With PostgreSQL, the table will remain empty after executing this piece of code, because the server will rollback the whole transaction. To workaround this problem in PostgreSQL you can use SAVEPOINT as described below in the Solution.

Solution:

INFORMIX transaction handling commands are automatically converted to PostgreSQL instructions to start, validate or cancel transactions.

Regarding the transaction control instructions, the BDL applications do not have to be modified in order to work with PostgreSQL.

You must review the SQL statements inside BEGIN WORK / COMMIT WORK instruction and check if these can raise an SQL error. The SQL statements that can potentially raise an SQL error must be protected with a SAVEPOINT. If an error occurs, just rollback to the savepoint:

   CREATE TABLE tab1 ( k INT PRIMARY KEY, c CHAR(10) )
   WHENEVER ERROR CONTINUE
   BEGIN WORK
   INSERT INTO tab1 ( 1, 'abc' )
   CALL sql_protect()
   INSERT INTO tab1 ( 1, 'abc' ) -- PK constraint violation = SQL Error
   CALL sql_unprotect()
   COMMIT WORK
   ...

   FUNCTION sql_protect()
      IF NOT dbtype == "PGS" THEN RETURN END IF
      EXECUTE IMMEDIATE "SAVEPOINT _sql_protect_"
   END FUNCTION

   FUNCTION sql_unprotect()
      IF NOT dbtype == "PGS" THEN RETURN END IF
      IF SQLCA.SQLCODE < 0 THEN
         EXECUTE IMMEDIATE "ROLLBACK TO SAVEPOINT _sql_protect_"
      ELSE
         EXECUTE IMMEDIATE "RELEASE SAVEPOINT _sql_protect_"
      END IF
   END FUNCTION

Note: If you want to use savepoints, do not use the UNIQUE keyword in the savepoint declaration, always specify the savepoint name in ROLLBACK TO SAVEPOINT, and do not drop savepoints with RELEASE SAVEPOINT.

Handling SQL errors when preparing statements

The PostgreSQL connector is implemented with the PostgreSQL libpq API. This library does not provide a way to send SQL statements to the database server during the BDL PREPARE instruction, like the INFORMIX interface does. The statement is sent to the server only when opening the cursors or when executing the statement, because the database driver needs to provide the data types of the SQL parameters (only known at OPEN / EXECUTE time).

Therefore, when preparing an SQL statement with the BDL PREPARE instruction, no SQL errors can be returned if the statement has syntax errors or if a column or a table name does not exist in the database. However, an SQL error will occur after the OPEN or EXECUTE instructions.

Solution:

Check that your BDL programs do not test STATUS or SQLCA.SQLCODE variable just after PREPARE instructions.

Change the program logic in order to handle the SQL errors when opening the cursors (OPEN) or when executing SQL statements (EXECUTE).

BOOLEAN data type

INFORMIX supports the BOOLEAN data type, which can store 't' or 'f' values. Genero BDL implements the BOOLEAN data type in a different way; as in other programming languages, Genero BOOLEAN stores integer values 1 or 0 (for TRUE or FALSE). The type was designed this way to assign the result of a Boolean expression to a BOOLEAN variable.

PostgreSQL supports the BOOLEAN data type and stores 't' or 'f' values for TRUE and FALSE representation. It is not possible to insert the integer values 1 or 0; values must be true, false, '1' or '0'.

Solution:

The PostgreSQL database interface supports the BOOLEAN data type, and converts the BDL BOOLEAN integer values to a CHAR(1) of 't' or 'f'.

CHARACTER data types

INFORMIX supports the following character data types:

CHAR(N) with N<= 32767 bytes
VARCHAR(N[,M]) with N<=255 bytes
NCHAR(N) with N<= 32767 bytes
NVARCHAR(N[,M]) with N<=255 bytes
LVARCHAR(N), without the 255 bytes limit (max size varies according to IDS version)

In INFORMIX, both CHAR/VARCHAR and NCHAR/NVARCHAR data types can be used to store single-byte or multi-byte encoded character strings. The only difference between CHAR/VARCHAR and NCHAR/NVARCHAR is for sorting: N[VAR]CHAR types use the collation order, while [VAR]CHAR types use the byte order. The character set used to store strings in CHAR/VARCHAR/NCHAR/NVARCHAR columns is defined by the DB_LOCALE environment variable. The character set used by applications is defined by the CLIENT_LOCALE environment variable. Note that INFORMIX uses Byte Length Semantics (the size N that you specify in [VAR]CHAR(N) is expressed in bytes, not characters as in some other databases)

PostgreSQL provides the following character types:

CHAR(N) with N<= 10485760 characters
VARCHAR(N) with N<= 10485760 characters; The length specification is optional.
TEXT with a limit of 1GB

In PostgreSQL, CHAR, VARCHAR and TEXT types store data in single byte or multi-byte character sets. For CHAR and VARCHAR, the size is specified in a number of characters, not bytes. The character set used to store data for these types is defined by the database character set, which can be specified when you create the database with the createdb tool or the CREATE DATABASE SQL command.

Note: The VARCHAR type of PostgreSQL can be used without a length specification. If no size is specified, the column accepts strings of any size. However, as Genero BDL needs to know the size of CHAR and VARCHAR columns to define fields and program variables from a schema file, you should not create tables in PostgreSQL having VARCHAR columns without size specification. If you try to extract a schema with fgldbsch, this tool will report that the VARCHAR column cannot be converted to a BDL type for the .sch file.

Automatic character set conversion between the PostgreSQL client and server is supported. You must properly specify the client character set for PostgreSQL. This can be done in different ways, with the SET CLIENT_ENCODING TO SQL command for example, or with configuration parameters. See the PostgreSQL documentation for more details.

Solution:

INFORMIX CHAR(N) types must be mapped to PostgreSQL CHAR(N) types, and INFORMIX VARCHAR(N) or LVARCHAR(N) columns must be mapped to PostgreSQL VARCHAR(N).

Note: When creating a table from 4gl with NCHAR or NVARCHAR types, the type names will be left as is and produce an SQL error because these types are not supported by PostgreSQL.

Keep in mind that PostgreSQL uses Character Length Semantics regarding CHAR/VARCHAR sizes: When you define a CHAR(20) and the database character set is multi-byte, the PostgreSQL column can hold more bytes/characters than the INFORMIX CHAR(20) type.

in UTF-8, you can store 20 � (e-acute) characters in PGS CHAR(20), but IFX CHAR(20) can only store 10 of such characters, because in UTF-8, � is encoded with 2 bytes. Even if Genero uses Byte Length Semantics when you define a CHAR/VARCHAR variable, a good practice is to use the same sizes for PostgreSQL CHAR/VARCHAR columns: You could then store more characters in the PGS columns than the Genero variable can hold, but this is not a problem.

You can store single-byte or multi-byte character strings in PostgreSQL CHAR, VARCHAR and TEXT columns.

Do not forget to properly define the client character set, which must correspond to the runtime system character set.

See also the section about Localization.

The LENGTH( ) function

PostgreSQL raises an error if the LENGTH() parameter is NULL. INFORMIX returns zero instead.

Solution:

The PostgreSQL database interface cannot simulate the behavior of the INFORMIX LENGTH() SQL function.

Review the program logic and make sure you do not pass NULL values to the LENGTH() SQL function.

Constraints

Constraint naming syntax:

Both INFORMIX and PostgreSQL support primary key, unique, foreign key, default and check constraints, but the constraint naming syntax is different. PostgreSQL expects the "CONSTRAINT" keyword before the constraint specification and INFORMIX expects it after.

UNIQUE constraint example:

INFORMIX	PostgreSQL
CREATE TABLE scott.emp ( ... empcode CHAR(10) UNIQUE [CONSTRAINT pk_emp], ...	CREATE TABLE scott.emp ( ... empcode CHAR(10) [CONSTRAINT pk_emp] UNIQUE, ...

Unique constraints:

Note: When using a unique constraint, INFORMIX allows only one row with a NULL value, while PostgreSQL allows several rows with NULL!

Solution:

The database interface does not convert constraint naming expressions when creating tables from BDL programs. Review the database creation scripts to adapt the constraint naming clauses for PostgreSQL.

Triggers

INFORMIX and PostgreSQL provide triggers with similar features, but the trigger creation syntax and the programming languages are totally different.

Solution:

INFORMIX triggers must be converted to PostgreSQL triggers "by hand".

Stored procedures

Both INFORMIX and PostgreSQL support stored procedures, but the programming languages are totally different. With PostgreSQL you must create the stored procedure language before writing triggers or stored procedures.

Solution:

INFORMIX stored procedures must be converted to PostgreSQL manually.

Defining database users

Until version 11.70.xC2, INFORMIX database users had to be created at the operating system level and be members of the 'informix' group. Starting with 11.70.xC2, INFORMIX supports database-only users with the CREATE USER instruction, as in most other db servers. Any database user must have sufficient privileges to connect and use resources of the database; user rights are defined with the GRANT command.

PostgreSQL users must be registered in the database. They are created by the createuser utility:

$ createuser --username=<username> --password

Solution:

Based on the application logic (is it a multi-user application ?), you have to create one or several PostgreSQL users.

Setting privileges

INFORMIX and PostgreSQL user privileges management are quite similar.

PostgreSQL provides user groups to grant or revoke permissions to more than one user at the same time.

Temporary tables

INFORMIX temporary tables are created through the CREATE TEMP TABLE DDL instruction or through a SELECT ... INTO TEMP statement. Temporary tables are automatically dropped when the SQL session ends, but they can be dropped with the DROP TABLE command. There is no name conflict when several users create temporary tables with the same name.

INFORMIX allows you to create indexes on temporary tables. No name conflict occurs when several users create an index on a temporary table by using the same index identifier.

PostgreSQL support temporary tables as INFORMIX does, with a little syntax difference in the SELECT INTO TEMP instruction.

Solution:

Temporary tables are well supported with native PostgreSQL temp tables.

Substrings in SQL

INFORMIX SQL statements can use subscripts on columns defined with the character data type:

    SELECT ... FROM tab1 WHERE col1[2,3] = 'RO'
    SELECT ... FROM tab1 WHERE col1[10] = 'R'   -- Same as col1[10,10]
    UPDATE tab1 SET col1[2,3]= 'RO' WHERE ...
    SELECT ... FROM tab1 ORDER BY col1[1,3]

.. while PostgreSQL provides the SUBSTR( ) function, to extract a substring from a string expression:

SELECT .... FROM tab1 WHERE SUBSTRING(col1 from 2 for 2) = 'RO'
SELECT SUBSTRING('Some text' from 6 for 3) ... -- Gives 'tex'

Solution:

You must replace all INFORMIX col[x,y] expressions by SUBSTRING( col from x for (y-x+1) ).

Notes:

In UPDATE instructions, setting column values through subscripts will produce an error with PostgreSQL :
    UPDATE tab1 SET col1[2,3] = 'RO' WHERE ...
     is converted to:
    UPDATE tab1 SET SUBSTRING(col1 from 2 for (3-2+1)) = 'RO' WHERE ...
Column subscripts in ORDER BY expressions are also converted and produce an error with PostgreSQL:
    SELECT ... FROM tab1 ORDER BY col1[1,3]
     is converted to:
    SELECT ... FROM tab1 ORDER BY SUBSTRING(col1 from 1 for(3-1+1))

Name resolution of SQL objects

INFORMIX uses the following form to identify an SQL object :

[database[@dbservername]:][{owner|"owner"}.]identifier

With PostgreSQL, an object name takes the following form:
[owner.]identifier

Solution:

As a general rule, to write portable SQL, you should only use simple database object names without any database, server or owner qualifier and without quoted identifiers.

String delimiters

The ANSI string delimiter character is the single quote ( 'string'). Double quotes are used to delimit database object names ("object-name").

Example: WHERE "tabname"."colname" = 'a string value'

INFORMIX allows double quotes as string delimiters, but PostgreSQL doesn't. This is important since many BDL programs use that character to delimit the strings in SQL commands.

Note: This problem concerns only double quotes within SQL statements. Double quotes used in pure BDL string expressions are not subject to SQL compatibility problems.

Solution:

The PostgreSQL database interface can automatically replace all double quotes by single quotes.

Escaped string delimiters can be used inside strings like following:

     'This is a single quote: '''
     'This is a single quote : \''
     "This is a double quote : """
     "This is a double quote : \""

Database object names cannot be delimited by double quotes because the database interface cannot determine the difference between a database object name and a quoted string. For example, if the program executes the SQL statement:
WHERE "tabname"."colname" = "a string value"
replacing all double quotes by single quotes would produce:
WHERE 'tabname'.'colname' = 'a string value'
This would produce an error since 'tabname'.'colname' is not allowed by PostgreSQL.

Although double quotes are replaced automatically in SQL statements, you should use only single quotes to enforce portability.

NUMERIC data types

INFORMIX supports several data types to store numbers:

INFORMIX Data Type	Description
SMALLINT	16 bit signed integer
INT / INTEGER	32 bit signed integer
BIGINT	64 bit signed integer
INT8	64 bit signed integer (replaced by BIGINT)
DEC / DECIMAL	Equivalent to DECIMAL(16)
DEC / DECIMAL(p)	Floating-point decimal number
DEC / DECIMAL(p,s)	Fixed-point decimal number
MONEY	Equivalent to DECIMAL(16,2)
MONEY(p)	Equivalent to DECIMAL(p,2)
MONEY(p,s)	Equivalent to DECIMAL(p,s)
REAL / SMALLFLOAT	32-bit floating point decimal (C float)
DOUBLE PRECISION / FLOAT[(n)]	64-bit floating point decimal (C double)

Solution:

PostgreSQL supports the following data types to store numbers:

PostgreSQL data type	Description
NUMERIC(p,s) / DECIMAL(p,s)	Decimals with precision and scale (fractional part)
NUMERIC(p) / DECIMAL(p)	Integers with p digits (no fractional part)
NUMERIC / DECIMAL	Floating point numbers (no limit)
FLOAT4	16 bit variable precision
FLOAT8	32 bit variable precision
INT2	16 bit signed integer
INT4	32 bit signed integer
INT8/BIGINT	64 bit signed integer

ANSI types like SMALLINT, INTEGER, FLOAT are supported by PostgreSQL as aliases to INT2, INT4 and FLOAT8 native types.

INFORMIX DECIMAL(p) floating point types are converted to DECIMAL without precision/scale, to store any floating point number in PostgreSQL.

Using column aliases in SELECT

PostgreSQL expects the ANSI notation for column aliases :

SELECT col1 AS col1_alias FROM ...

INFORMIX supports the ANSI notation.

Solution:

The database interface cannot convert INFORMIX alias specification to the ANSI notation.

Review your programs and replace the INFORMIX notation with the ANSI form.

MATCHES and LIKE in SQL conditions

INFORMIX supports MATCHES and LIKE in SQL statements. PostgreSQL supports the LIKE statement as in INFORMIX, plus the ~ operators that are similar but different from the INFORMIX MATCHES operator.

MATCHES allows brackets to specify a set of matching characters at a given position :
( col MATCHES '[Pp]aris' ).
( col MATCHES '[0-9][a-z]*' ).
In this case, the LIKE statement has not equivalent feature.

The following substitutions must be made to convert a MATCHES condition to a LIKE condition :

MATCHES keyword must be replaced by LIKE.
All '*' characters must be replaced by '%'.
All '?' characters must be replaced by '_'.
Remove all brackets expressions.

PostgreSQL ~ operator expects regular expressions as follows:
( col ~ 'a.*' )

Solution:

SQL statements using MATCHES expressions must be reviewed in order to use LIKE expressions.

INFORMIX specific SQL statements in BDL

The BDL compiler supports several INFORMIX specific SQL statements that have no meaning when using PostgreSQL.

CREATE DATABASE
DROP DATABASE
START DATABASE (SE only)
ROLLFORWARD DATABASE
SET [BUFFERED] LOG
CREATE TABLE with special options (storage, lock mode, etc.)

Solution:

Review your BDL source and remove all static SQL statements which are INFORMIX specific.

INSERT cursors

INFORMIX supports insert cursors. An "insert cursor" is a special BDL cursor declared with an INSERT statement instead of a SELECT statement. When this kind of cursor is open, you can use the PUT instruction to add rows and the FLUSH instruction to insert the records into the database.

For INFORMIX database with transactions, OPEN, PUT and FLUSH instructions must be executed within a transaction.

PostgreSQL does not support insert cursors.

Solution:

Insert cursors are emulated by the PostgreSQL database interface.

Very large data types

Both INFORMIX and PostgreSQL Server provide special data types to store very large texts or images:

INFORMIX Data Type	PostgreSQL Data Type
TEXT	TEXT
BYTE	BYTEA

Solution:

TEXT and BYTE data can be stored in PostgreSQL TEXT and BYTEA columns.

Cursors WITH HOLD

INFORMIX closes opened cursors automatically when a transaction ends unless the WITH HOLD option is used in the DECLARE instruction. In PostgreSQL, opened cursors using SELECT statements without a FOR UPDATE clause are not closed when a transaction ends. Actually, all PostgreSQL cursors are 'WITH HOLD' cursors unless the FOR UPDATE clause is used in the SELECT statement.

Cursors declared FOR UPDATE and using the WITH HOLD option cannot be supported with PostgreSQL because FOR UPDATE cursors are automatically closed by PostgreSQL when the transaction ends.

Solution:

BDL cursors that are not declared "WITH HOLD" are automatically closed by the database interface when a COMMIT WORK or ROLLBACK WORK is performed.

Since PostgreSQL automatically closes FOR UPDATE cursors when the transaction ends, opening cursors declared FOR UPDATE and the WITH HOLD option results in an SQL error that does not normally appear with INFORMIX under the same conditions. Review the program logic in order to find another way to set locks.

UPDATE/DELETE WHERE CURRENT OF <cursor>

INFORMIX allows positioned UPDATEs and DELETEs with the "WHERE CURRENT OF <cursor>" clause, if the cursor has been DECLARED with a SELECT ... FOR UPDATE statement.

PGS 8.1 and 8.2: UPDATE/DELETE ... WHERE CURRENT OF <cursor> is not supported by PostgreSQL. However, you can use the OID column to do positioned updates/deletes.

Since PGS 8.3: UPDATE/DELETE ... WHERE CURRENT OF <cursor> is supported by PostgreSQL with server-side cursors created with a DECLARE statement.

Solution:

With PostgreSQL 8.1 (dbmpgs81x) and 8.2 (dbmpgs82x):

UPDATE/DELETE ... WHERE CURRENT OF instructions are managed by the PostgreSQL database interface. The PostgreSQL database interface replaces "WHERE CURRENT OF <cursor>" by "WHERE OID = ?" and sets the value of the Object Identifier returned by the last FETCH done with the given cursor..

PGS 8.1 and 8.2 only: Starting with PostgreSQL version 8.1, user tables do not get the oid column by default. You must set the default_with_oid configuration parameter in the postgresql.conf file.

With PostgreSQL 8.3 (dbmpgs83x) and higher:

UPDATE/DELETE ... WHERE CURRENT OF instructions are executed as is. Since SELECT FOR UPDATE statements are now executed with a server cursor by using a DECLARE PostgreSQL statement, native positioned update/delete takes place.

Querying system catalog tables

As in INFORMIX, PostgreSQL provides system catalog tables (actually, system views). But the table names and their structure are quite different.

Solution:

No automatic conversion of INFORMIX system tables is provided by the database interface.

Syntax of UPDATE statements

INFORMIX allows a specific syntax for UPDATE statements:

UPDATE table SET ( <col-list> ) = ( <val-list> )

UPDATE table SET table.* = myrecord.*
UPDATE table SET * = myrecord.*

Solution:

Static UPDATE statements using the above syntax are converted by the compiler to the standard form:

UPDATE table SET column=value [,...]

The LENGTH() function

INFORMIX provides the LENGTH() function:

SELECT LENGTH("aaa"), LENGTH(col1) FROM table

PostgreSQL has a equivalent function with the same name, but there is some difference:

INFORMIX does not count the trailing blanks for CHAR or VARCHAR expressions, while PostgreSQL counts the trailing blanks.

With the PostgreSQL LENGTH function, when using a CHAR column values are always blank padded, so the function returns the size of the CHAR column. When using a VARCHAR column, trailing blanks are significant, and the function returns the number of characters, including trailing blanks.

Solution:

You must check if the trailing blanks are significant when using the LENGTH() function.

If you want to count the number of character by ignoring the trailing blanks, you must use the RTRIM() function:

INTERVAL data type

The INFORMIX INTERVAL data type stores a value that represents a span of time. INTERVAL types are divided into two classes : year-month intervals and day-time intervals.

Starting with version 8.4, PostgreSQL provides an INTERVAL data type which is equivalent to the Informix INTERVAL type. The following are some features of the PostgreSQL 8.4 interval type:

It is possible to specify the interval class / precision with YEAR, MONTH, DAY, HOUR, MINUTE and SECOND[(p)] fields.
Fractional part of seconds can be defined with up to 6 digits.
The INTERVALs value range is from -178000000 to +178000000 years.
Input and output format can be controlled with the SET intervalstyle command.

Solution:

Starting with Genero 2.21, database drivers dbmpgs84x and higher convert the Informix-style INTERVAL type to the native PostgreSQL INTERVAL type. See the data type conversion table for the exact conversion rules.

Important; The PostgreSQL database driver forces the intervalstyle session parameter to 'iso_8601', this is required to insert and fetch interval database with the libpq C API functions. You must not change this setting during program execution.

While PostgreSQL INTERVALs support up to 9 digits for the higher unit like Informix, YEAR values range from -178000000 to +178000000 only. This limitation exists in PostgreSQL 8.4 and may be solved in future versions.

With PostgreSQL and driver versions prior to 8.4, the INTERVAL data type is converted to CHAR(50).

Data storage concepts

An attempt should be made to preserve as much of the storage information as possible when converting from INFORMIX to PostgreSQL. Most important storage decisions made for INFORMIX database objects (like initial sizes and physical placement) can be reused for the PostgreSQL database.

Storage concepts are quite similar in INFORMIX and in PostgreSQL, but the names are different.

The LOAD and UNLOAD instructions

INFORMIX provides two SQL instructions to export / import data from / into a database table: The UNLOAD instruction copies rows from a database table into a text file and the LOAD instructions insert rows from a text file into a database table.

PostgreSQL does not provide LOAD and UNLOAD instructions.

Solution:

LOAD and UNLOAD instructions are supported.

SQL Interruption

With INFORMIX, it is possible to interrupt a long running query if the SQL INTERRUPT ON option is set by the Genero program. The database server returns SQLCODE -213, which can be trapped to detect a user interruption.

    MAIN
      DEFINE n INTEGER
      DEFER INTERRUPT
      OPTIONS SQL INTERRUPT ON
      DATABASE test1
      WHENEVER ERROR CONTINUE
      -- Start long query (self join takes time)
      -- From now on, user can hit CTRL-C in TUI mode to stop the query
      SELECT COUNT(*) INTO n FROM customers a, customers b
           WHERE a.cust_id <> b.cust_id
      IF SQLCA.SQLCODE == -213 THEN
         DISPLAY "Statement was interrupted by user..."
         EXIT PROGRAM 1
      END IF
      WHENEVER ERROR STOP
      ...
    END MAIN

PostgreSQL supports SQL Interruption in a similar way. The db client must issue an PQcancel() libPQ call to interrupt a query.

Solution:

The PostgreSQL database driver supports SQL interruption and converts the SQLSTATE code 57014 to the INFORMIX error code -213.

Scrollable Cursors

The Genero programming language supports scrollable cursors with the SCROLL keyword, as shown in the following code example:

   DECLARE c1 SCROLL CURSOR FOR SELECT * FROM customers ORDER BY cust_name
   ...
   FETCH FIRST c1 INTO rec_cust.*
   ...
   FETCH NEXT c1 INTO rec_cust.*
   ...
   FETCH LAST c1 INTO rec_cust.*

PostgreSQL supports native scrollable cursors.

Solution:

The PostgreSQL database driver uses native scrollable cursors by declaring (DECLARE) server cursors with the SCROLL clause.

Data type conversion table

INFORMIX Data Types	PostgreSQL Data Types (before 8.4)	PostgreSQL Data Types (since 8.4)
CHAR(n)	CHAR(n)	CHAR(n)
VARCHAR(n[,m])	VARCHAR(n)	VARCHAR(n)
LVARCHAR(n[,m])	VARCHAR(n)	VARCHAR(n)
NCHAR(n)	N/A	N/A
NVARCHAR(n[,m])	N/A	N/A
BOOLEAN	BOOLEAN	BOOLEAN
SMALLINT	INT2	INT2
INT / INTEGER	INT4	INT4
BIGINT	BIGINT	BIGINT
INT8	BIGINT	BIGINT
SERIAL[(start)]	INTEGER (see notes)	INTEGER (see notes)
BIGSERIAL[(start)]	BIGINT (see notes)	BIGINT (see notes)
SERIAL8[(start)]	BIGINT (see notes)	BIGINT (see notes)
DOUBLE PRECISION / FLOAT[(n)]	FLOAT4	FLOAT4
REAL / SMALLFLOAT	FLOAT8	FLOAT8
NUMERIC / DEC / DECIMAL(p,s)	DECIMAL(p,s)	DECIMAL(p,s)
NUMERIC / DEC / DECIMAL(p)	DECIMAL (no precision = floating point)	DECIMAL (no precision = floating point)
NUMERIC / DEC / DECIMAL	DECIMAL	DECIMAL
MONEY(p,s)	DECIMAL(p,s)	DECIMAL(p,s)
MONEY(p)	DECIMAL(p,2)	DECIMAL(p,2)
MONEY	DECIMAL(16,2)	DECIMAL(16,2)
DATE	DATE	DATE
DATETIME HOUR TO SECOND	TIME(0) WITHOUT TIME ZONE	TIME(0) WITHOUT TIME ZONE
DATETIME q1 TO q2 (different from above)	TIMESTAMP(p+1) WITHOUT TIME ZONE	TIMESTAMP(p+1) WITHOUT TIME ZONE
INTERVAL YEAR[(p)] TO MONTH	CHAR(50)	INTERVAL YEAR TO MONTH
INTERVAL YEAR[(p)] TO YEAR	CHAR(50)	INTERVAL YEAR
INTERVAL MONTH[(p)] TO MONTH	CHAR(50)	INTERVAL MONTH
INTERVAL DAY[(p)] TO FRACTION(n)	CHAR(50)	INTERVAL DAY TO SECOND(n)
INTERVAL DAY[(p)] TO SECOND	CHAR(50)	INTERVAL DAY TO SECOND(0)
INTERVAL DAY[(p)] TO MINUTE	CHAR(50)	INTERVAL DAY TO MINUTE
INTERVAL DAY[(p)] TO HOUR	CHAR(50)	INTERVAL DAY TO HOUR
INTERVAL DAY[(p)] TO DAY	CHAR(50)	INTERVAL DAY
INTERVAL HOUR[(p)] TO FRACTION(n)	CHAR(50)	INTERVAL HOUR TO SECOND(n)
INTERVAL HOUR[(p)] TO SECOND	CHAR(50)	INTERVAL HOUR TO SECOND(0)
INTERVAL HOUR[(p)] TO MINUTE	CHAR(50)	INTERVAL HOUR TO MINUTE
INTERVAL HOUR[(p)] TO HOUR	CHAR(50)	INTERVAL HOUR
INTERVAL MINUTE[(p)] TO FRACTION(n)	CHAR(50)	INTERVAL MINUTE TO SECOND(n)
INTERVAL MINUTE[(p)] TO SECOND	CHAR(50)	INTERVAL MINUTE TO SECOND(0)
INTERVAL MINUTE[(p)] TO MINUTE	CHAR(50)	INTERVAL MINUTE
INTERVAL SECOND[(p)] TO FRACTION(n)	CHAR(50)	INTERVAL SECOND(n)
INTERVAL SECOND[(p)] TO SECOND	CHAR(50)	INTERVAL SECOND(0)
INTERVAL FRACTION[(p)] TO FRACTION(n)	CHAR(50)	INTERVAL SECOND(n)
TEXT	TEXT	TEXT
BYTE	BYTEA	BYTEA

PostgreSQL version	Shared library environment setting
PostgreSQL 8.0 and higher	UNIX: Add $PGDIR/lib to LD_LIBRARY_PATH (or its equivalent). Windows: Add %PGDIR%\bin to PATH.