Informix to PostgreSQL Migration

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

  • SQLines SQL Converter - SQL scripts conversion and assessment tool
  • SQLines Data - Data transfer, schema migration and validation tool

Databases:

  • Informix 14.x, 12.x, 11.x, 10.x, 9.x and 7.x
  • PostgreSQL 16.x, 15.x, 14.x, 13.x, 12.x, 11.x, 10.x and 9.x

Migration Reference

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

Date and time data types:

Informix PostgreSQL
1 DATE Date (year, month and day) DATE
2 DATETIME YEAR TO SECOND Date and time TIMESTAMP(0)
DATETIME YEAR TO MINUTE
DATETIME YEAR TO HOUR
DATETIME YEAR TO DAY Date DATE
3 INTERVAL HOUR TO FRACTION(n) Interval INTERVAL HOUR TO SECOND(n)
INTERVAL SECOND TO SECOND Interval in seconds INT and EXTRACT(EPOCH...)
INTERVAL MINUTE TO MINUTE Interval in minutes INT and EXTRACT(EPOCH...)/60

Other data types:

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 DECIMAL(p,s), DEC(p,s) Fixed-point number DECIMAL(p,s), DEC(p,s)
10 DOUBLE PRECISION Double-precision floating-point number DOUBLE PRECISION
11 FLOAT(p) Double-precision floating-point number DOUBLE PRECISION
12 INTEGER, INT 32-bit integer INTEGER, INT
13 INT8 64-bit integer INT8
14 LVARCHAR(n) Variable-length string, 1 ⇐ n ⇐ 32739 VARCHAR(n)
15 MONEY(p,s) Currency amount MONEY
16 NCHAR(n) Fixed-length string, 1 ⇐ n ⇐ 32767 CHAR(n)
17 NUMERIC(p,s) Fixed-point number NUMERIC(p,s)
18 NVARCHAR(n,r) Variable-length string, 1 ⇐ n ⇐ 255 VARCHAR(n)
19 REAL Single-precision floating-point number REAL
20 SMALLFLOAT Single-precision floating-point number REAL
21 SMALLINT 16-bit integer SMALLINT
22 SERIAL(s) Auto-increment 32-bit integer SERIAL
23 SERIAL8(s) Auto-increment 64-bit integer SERIAL8
24 TEXT Character data, ⇐ 2 GB TEXT
25 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

SELECT Statement

Converting SQL queries:

Informix PostgreSQL
1 OUTER clause Outer join syntax ANSI SQL OUTER JOIN clause

CREATE TABLE Statement

Temporary table:

Informix PostgreSQL
1 CREATE TEMP TABLE name CREATE TEMP | TEMPORARY TABLE name
2 WITH NO LOG Without logging Removed, it's default
UNLOGGED is used for regular tables only

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