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 tools to help you migrate from Informix to PostgreSQL:
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.
Technical information on migration from Informix to PostgreSQL.
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 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 |
Converting SQL queries:
Informix | PostgreSQL | |||
1 | OUTER clause | Outer join syntax | ANSI SQL OUTER JOIN clause |
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 ![]() |
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.
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.
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:
Converting SQL statements from Informix to PostgreSQL:
Informix | PostgreSQL | ||
1 | GRANT privileges TO grantee AS grantor | Grant access privileges | GRANT privileges TO grantee ![]() |