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.

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

Informix to PostgreSQL Migration Tools

SQLines tools to help you migrate from Informix to PostgreSQL:

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.

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

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 SQL Converter

SQLines Data

Migration to MariaDB

Migration to Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to SingleStore

Migration to IBM DB2

Migration to Redshift

Migration to Snowflake

Migration to Apache Hive

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to EsgynDB

Application Conversion

Database Reference