This is an old revision of the document!


Informix to PostgreSQL Migration

SQLines provides tools and services to help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from Informix to PostgreSQL.

We also help convert embedded SQL statements in C/C++, C#, Java, PowerBuilder, VB/VB.NET, ASP/ASP.NET and Perl/PHP applications.

  • Informix 12.x, 11.x, 10.x, 9.x and 7.x
  • PostgreSQL 9.x and 8.x

Informix to PostgreSQL Migration Tools

SQLines SQL Converter Tool

SQLines SQL Converter tool allows you to convert database schema (DDL), queries and DML statements, views, stored procedures, functions and triggers from Informix to PostgreSQL.

SQLines tool converts SQL scripts and standalone SQL statements. To migrate data and database schema from an Informix database use SQLines Data tool.

SQLines tool is available in Online and Desktop editions.

Try SQLines Online or download a Trial Version.

Informix to PostgreSQL Migration Reference

Technical information on migration from Informix to PostgreSQL.

Last Update: Informix 11.70 and PostgreSQL 9.3

SQL Language Elements

Converting SQL language elements:

Informix PostgreSQL
1 { comment } /* comment */
2 ORDER BY NULLs in ORDER BY ORDER BY NULLS FIRST | LAST Different default order
3 String sorting order String sorting order Different default sorting
4 num UNITS SECOND Interval in seconds INTERVAL 'num' SECOND
var UNITS SECOND var * INTERVAL '1' SECOND

Data Types

Data type mapping from Informix to PostgreSQL:

Informix PostgreSQL
1 BIGINT 64-bit integer BIGINT
2 BIGSERIAL(s) Auto-increment 64-bit integer BIGSERIAL
3 BLOB Binary large object, ⇐ 4 TB BYTEA
4 BOOLEAN True, false or NULL BOOLEAN
5 BYTE Binary data, ⇐ 2 GB BYTEA
6 CHAR(n), CHARACTER(n) Fixed-length string, 1 ⇐ n ⇐ 32767 CHAR(n), CHARACTER(n)
7 CHARACTER VARYING(n,r) Variable-length string, 1 ⇐ n ⇐ 255 CHARACTER VARYING(n)
8 CLOB Character large object, ⇐ 4 TB TEXT
9 DATE Date (year, month and day) DATE
10 DATETIME YEAR TO SECOND Date and time TIMESTAMP(0)
DATETIME YEAR TO MINUTE
DATETIME YEAR TO HOUR
DATETIME YEAR TO DAY Date DATE
11 DECIMAL(p,s), DEC(p,s) Fixed-point number DECIMAL(p,s), DEC(p,s)
12 DOUBLE PRECISION Double-precision floating-point number DOUBLE PRECISION
13 FLOAT(p) Double-precision floating-point number DOUBLE PRECISION
14 INTEGER, INT 32-bit integer INTEGER, INT
15 INT8 64-bit integer INT8
16 INTERVAL SECOND TO SECOND Interval in seconds INT and EXTRACT(EPOCH...)
INTERVAL MINUTE TO MINUTE Interval in minutes INT and EXTRACT(EPOCH...)/60
17 LVARCHAR(n) Variable-length string, 1 ⇐ n ⇐ 32739 VARCHAR(n)
18 MONEY(p,s) Currency amount MONEY
19 NCHAR(n) Fixed-length string, 1 ⇐ n ⇐ 32767 CHAR(n)
20 NUMERIC(p,s) Fixed-point number NUMERIC(p,s)
21 NVARCHAR(n,r) Variable-length string, 1 ⇐ n ⇐ 255 VARCHAR(n)
22 REAL Single-precision floating-point number REAL
23 SMALLFLOAT Single-precision floating-point number REAL
24 SMALLINT 16-bit integer SMALLINT
25 SERIAL(s) Auto-increment 32-bit integer SERIAL
26 SERIAL8(s) Auto-increment 64-bit integer SERIAL8
27 TEXT Character data, ⇐ 2 GB TEXT
28 VARCHAR(n,r) Variable-length string, 1 ⇐ n ⇐ 255 VARCHAR(n)

Data type attributes and options

Informix PostgreSQL
1 column BYTE IN TABLE column BYTEA
2 column BYTE IN lob_space column BYTEA
3 column TEXT IN TABLE column TEXT
4 column TEXT IN lob_space column TEXT

CREATE PROCEDURE Statement

Converting stored procedures from Informix to PostgreSQL:

Informix PostgreSQL
1 CREATE PROCEDURE name CREATE OR REPLACE FUNCTION name
2 OUT | INOUT param datatype(len) DEFAULT default IN | OUT | INOUT param datatype(len) DEFAULT | = default
3 If RETURN is not specified RETURNS VOID is added
4 No AS keyword before the statements block AS is added
5 No BEGIN keyword to start the statements block BEGIN is added after DECLARE section
6 END PROCEDURE; End of procedure block END; $$ LANGUAGE plpgsql;

For more information, see Conversion of Procedural SPL statements from Informix to PostgreSQL.

CREATE FUNCTION Statement

Converting user-defined functions from Informix to PostgreSQL:

Informix PostgreSQL
1 CREATE FUNCTION name CREATE OR REPLACE FUNCTION name
2 OUT | INOUT param datatype(len) DEFAULT default IN | OUT | INOUT param datatype(len) DEFAULT | = default
3 After a parameter with DEFAULT, other parameters
can be without DEFAULT
DEFAULT must be specified for all following parameters
4 RETURNING datatype Scalar return value RETURNS datatype
5 No AS keyword before the statements block AS is added
6 No BEGIN keyword to start the statements block BEGIN is added after DECLARE section
7 END FUNCTION; End of function block END; $$ LANGUAGE plpgsql;

For more information, see Conversion of Procedural SPL statements from Informix to PostgreSQL.

Procedural SQL Statements

Converting procedural SQL statements (SPL) used in stored procedures, functions and triggers from Informix to PostgreSQL:

Variable declaration and assignment:

Informix PostgreSQL
1 DEFINE var datatype(len); Variable declaration var datatype(len);
DEFINE var, var2, … datatype(len); var datatype(len); var2 datatype(len); …
2 LET var = value; Assignment statement var := value
LET var, var2, … = value, value2, …; var := value; var2 := value2; …
LET var, var2 = (SELECT c1, c2 FROM …) SELECT c1, c2 INTO var, var2 FROM …

Flow-of-control statements:

Informix PostgreSQL
1 FOREACH cur FOR select INTO vars stmt
END FOREACH
Query loop DECLARE cur FOR select;
FOR vars IN cur LOOP stmt END LOOP;
FOREACH select INTO vars stmt
END FOREACH
FOR vars IN select LOOP stmt END LOOP;
2 IF condition THEN … END IF; IF statement IF condition THEN … END IF;

Other statements:

Informix PostgreSQL
1 BEGIN [WORK]; Start a transaction BEGIN [WORK |TRANSACTION];
3 ON EXCEPTION stmt END EXCEPTION; Exception handler EXCEPTION WHEN OTHERS THEN stmt
6 ROLLBACK [WORK]; Rollback the transaction ROLLBACK [WORK |TRANSACTION];

SQL Statements

Converting SQL statements from Informix to PostgreSQL:

Informix PostgreSQL
1 GRANT privileges TO grantee AS grantor Grant access privileges GRANT privileges TO grantee

Migration Validation