ODIPGS001 - 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 dates :

• 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(5) can be stored in PostgreSQL TIMESTAMP(5) 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".

Warning : SQL Statements using expressions with TODAY / CURRENT /  EXTEND must be reviewed and adapted to the native syntax.

ODIPGS003 - 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.

ODIPGS004 - ROWIDs

PostgreSQL tables are automatically created with a OID column (Object Identifier) of type INTEGER. The behavior is equivalent to INFORMIX ROWID columns.

Solution :

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

Warning: 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.

ODIPGS005 - SERIAL data type

INFORMIX SERIAL data type and automatic number production :

• The table column must be of type SERIAL.
• To generate a new serial, no value or zero value is given to 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].
• 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:

• This data type has the same name but is NOT the same as INFORMIX SERIALs.
• You cannot define a start value ( SERIAL(100) ).
• You must omit the column name when inserting rows because the technique is based on default values.
• You can insert a specific value but the next serial will be generated from the sequence.
• 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') FROM DUAL

Solution :

The INFORMIX SERIAL data type can be emulated with three methods, according to FGLPROFILE settings:

1. When dbi.database.<dbname>.ifxemul.datatype.serial.emulation = "native" (the default), the native PostgreSQL SERIAL data type is used. The sqlca.sqlerrd[2] register is NOT filled as expected.
2. When dbi.database.<dbname>.ifxemul.datatype.serial.emulation = "regtable", 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.
3. When dbi.database.<dbname>.ifxemul.datatype.serial.emulation = "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.

Warning : When using solution 1 (which is the default), the sqlca.sqlerrd[2] register is not set after an INSERT. You must configure FGLPROFILE to use solution 2 or 3 when you need this feature.

When using solution 2 or 3, 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. However, when using solution 2, the table SERIALREG 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.

The gsrl_pgs tool provided in the Adaptation Kit for PostgreSQL can generate the SQL script to create the serial triggers from a set of tables. This tool takes a file containing a list of table names and column names for which a SERIAL column must be emulated. See the gsrl_pgs tool for more details (run "gsrl_pgs -h").

ODIPGS006 - Outer joins

In INFORMIX SQL, outer tables are 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 :

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

Prerequisites :

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

Restrictions :

1. 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".
2. Statements composed of 2 or more SELECT instructions using OUTERs are not supported.
     Example : "SELECT ... UNION SELECT" or "SELECT ... WHERE col IN (SELECT...)"

Remarks :

1. Table aliases are detected in OUTER expressions.
      OUTER example with table alias : "OUTER( tab1 alias1)".
2. 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 ".
3. Table names detection is not case-sensitive.
      Example : "SELECT ... FROM tab1, TAB2 WHERE tab1.col1 = tab2.col2".
4. Temporary tables are supported in OUTER specifications.

ODIPGS007a - 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. 

ODIPGS008a - Data consistency and concurrency management

Data consistency involves readers which 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 LEVEL 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 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 :

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 INFORMIX and PostgreSQL documentation for more details about data consistency, concurrency and locking mechanisms.

ODIPGS008b - 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.

Warning : PostgreSQL has no equivalent for "SET LOCK MODE TO NOT WAIT".

Solution :

Warning : 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.

ODIPGS009 - 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".
• Statements executed outside of a transaction are automatically committed.
• DDL statements can be executed (and canceled) in transactions.

PostgreSQL :

• Transactions are started with "BEGIN WORK".
• Transactions are validated with "COMMIT WORK".
• Transactions are canceled with "ROLLBACK WORK".
• Statements executed outside of a transaction are automatically committed.
• DDL statements can be executed (and canceled) in transactions.

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

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.

ODIPGS010 - 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. The statement is sent to the server only when opening the cursors or when executing the statement.

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).

ODIPGS011a - CHARACTER data types

As in INFORMIX, PostgreSQL provides the CHAR and VARCHAR data types to store character data.

INFORMIX CHAR type can store up to 32767 characters and the VARCHAR data type is limited to 255 characters.

Since PostgreSQL CHAR and VARCHAR have a size limit of 1GB.

Solution :

The database interface supports character string variables in SQL statements for input (BDL USING) and output (BDL INTO).

ODIPGS011b - The LENGTH( ) function

Warning : 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.

ODIPGS012 - 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 :

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.

ODIPGS013 - 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".

ODIPGS014 - 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 "by hand".

ODIPGS016a - Defining database users

INFORMIX users are defined at the operating system level, they must be members of the 'informix' group, and the database administrator must grant CONNECT, RESOURCE or DBA privileges to those users.

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

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

Solution :

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

ODIPGS016b - 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.

ODIPGS017 - 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.

ODIPGS018 - 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) ).

Warning : 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 ...

Warning : 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))

ODIPGS019 - Name resolution of SQL objects

INFORMIX uses the following form to identify an SQL object :

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

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

Solution :

Check for single or double quoted table or column names in your source and remove them.

ODIPGS020 - 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.

Remark : 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 : \""

Warning : 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.

ODIPGS021 - NUMERIC data types

INFORMIX supports several data types to store numbers :

Solution :

PostgreSQL supports the following data types to store numbers :

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

ODIPGS022 - 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 :

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

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

If both db support the ANSI notation, is replacement necessary?

ODIPGS024 - 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 :

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

ODIPGS025 - INFORMIX specific SQL statements in BDL

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

(removed a sentence as unnecessary)

• 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.

ODIPGS028 - 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.

ODIPGS030 - Very large data types

Both INFORMIX and Microsoft SQL Server provide special data types to store very large texts or images, but the names are different:

Solution :

Very large character data types are not supported yet by the PostgreSQL database interface.

ODIPGS031 - 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.

Warning : 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.

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

ODIPGS032 - 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.

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

Solution :

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..

Warning : 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. 

ODIPGS033 - 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 :

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

ODIPGS034 - Syntax of UPDATE statements

INFORMIX allows a specific syntax for UPDATE statements :

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

or

    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 [,...]

ODIPGS035 - 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 neither for CHAR not for 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:

ODIPGS036 - INTERVAL data type

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.

PostgreSQL provides an INTERVAL data type, but it is totally different from the INFORMIX INTERVAL type. For example, you specify a INTERVAL literal as follows :

    25 years 2 months 23 days

Solution :

Warning: The INTERVAL data type is not well supported because the database server has no equivalent native data type. However, you can store into and retrieve from CHAR columns BDL INTERVAL values.

ODIPGS039 - 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.

ODIPGS040 - National characters data types

INFORMIX : NCHAR & NVARCHAR
PostgreSQL : ?

Solution :

Warning : National character data types are not supported yet.

ODIPGS046 - 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, but provides external tools like SQL*Plus and SQL*Loader.

Solution :

LOAD and UNLOAD instructions are supported.

Warning : There is a difference when using PostgreSQL DATE columns : DATE columns created in the PostgreSQL database are similar to INFORMIX DATETIME YEAR TO SECOND columns. In LOAD and UNLOAD, all PostgreSQL DATE columns are treated as INFORMIX DATETIME YEAR TO SECOND columns and thus will be unloaded with the "YYYY-MM-DD hh:mm:ss"  format.
The same problem appears for INFORMIX INTEGER and SMALLINT values which are stored in a PostgreSQL database as NUMBER(?) columns. Those values will be unloaded as INFORMIX DECIMAL(10) and DECIMAL(5) values, that is, with a trailing dot-zero ".0".

Warning : When using an INFORMIX database, simple dates are unloaded with the DBDATE format (ex: "23/12/1998"). Therefore, unloading from an INFORMIX database for loading into an PostgreSQL database is not supported.

Warning : UNLOAD instructions based on SELECT statements using date expressions as "WHERE ?-datecol>?" are not supported because of database server limitations. A syntax error would be raised in this case.

ODIPGS100 - Data type conversion table