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 17.x, 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);
1	DEFINE var, var2, … datatype(len);	Variable declaration	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;
1	FOREACH select INTO vars stmt END FOREACH	Query loop	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

Data Transfer Validation

SQLines Data

SQLines SQL Converter

Migration to MariaDB

Migration to MySQL

Migration to PostgreSQL

Migration to SQL Server

Migration to Oracle

SQL in Applications