Runtime configuration
Database concepts
| Database concepts |
| Data storage concepts |
| Data consistency and concurrency management |
| Transactions handling |
| Defining database users |
| Setting privileges |
Data dictionary
Data manipulation
BDL programming
Install PostgreSQL and create a database
Compile and install the PostgreSQL Server on your computer. PostgreSQL is a free database, you can download the sources from www.postgresql.org.
Set configuration parameters in postgresql.conf:
Warning for PGS 8.1 and 8.2: UPDATE / DELETE WHERE CURRENT OF needs oid column support. Starting with PostgreSQL version 8.1, user tables do not get the oid column by default. You must set the default_with_oid configuration parameter to "on" in order to get oid columns created. You do no more need to set this parameter with PostgreSQL 8.3 when using the dbmpgs83x driver.
Start a postmaster process to listen to database client connections.
Warning: If you want to connect through TCP (for example from a Windows PostgreSQL client), you must start postmaster with the -i option and setup the pg_hba.conf file for security (trusted hosts and users).Create a PostgreSQL database with the createdb utility:
$ createdb -h hostname dbnameIf you plan to use SERIAL emulation, you must create the plpgsql procedure language, because the database interface uses this language to create serial triggers:
$ createlang -h hostname plpgsql dbnameConnect to the database as the administrator user and create a database user dedicated to your application, the application administrator:
dbname=# CREATE USER appadmin PASSWORD 'password';
CREATE USER
dbname=# GRANT ALL PRIVILEGES ON DATABASE dbname TO appadmin;
GRANT
dbname=# \qCreate the application tables. Do not forget to convert Informix data types to PostgreSQL data types. See issue ODIPGS100 for more details.
If you plan to use the SERIAL emulation, you must prepare the database. See issue ODIPGS005 for more details.
Prepare the runtime environment
The PostgreSQL client software is required to connect to a database server. Check if the PostgreSQL client library (libpq.*) is installed on the machine where the 4gl programs run.
Set up the fglprofile entries for database connections
In order to connect to PostgreSQL, you must have a database driver "dbmpgs*" installed.
Warning: On HP/UX LP64, the database driver must be linked with the libxnet library if you want to use networking.
INFORMIX provides two data types to store dates and time information:
PostgreSQL provides the following data type to store dates:
String representing date time information:
INFORMIX is able to convert quoted strings to DATE / DATETIME data if the string contents matches environment parameters (i.e. DBDATE, GL_DATETIME). As in INFORMIX, PostgreSQL can convert quoted strings to date time data according to the DateStyle session parameter. PostgreSQL always accepts ISO date time strings.
Date arithmetic:
Solution:
PostgreSQL has the same DATE data type as INFORMIX ( year, month, day ). So you can use PostgreSQL DATE data type for Informix DATE columns.
PostgreSQL TIME(0) WITHOUT TIME ZONE data type can be used to store INFORMIX DATETIME HOUR TO SECOND values. The database interface makes the conversion automatically.
INFORMIX DATETIME values with any precision from YEAR to FRACTION(5) can be stored in PostgreSQL TIMESTAMP(5) WITHOUT TIME ZONE columns. The database interface makes the conversion automatically. Missing date or time parts default to 1900-01-01 00:00:00.0. For example, when using a DATETIME HOUR TO MINUTE with the value of "11:45", the PostgreSQL TIMESTAMP value will be "1900-01-01 11:45:00.0".
Warning: SQL Statements using expressions with TODAY / CURRENT / EXTEND must be reviewed and adapted to the native syntax.
Warning: Literal DATETIME and INTERVAL expressions (i.e. DATETIME ( 1999-10-12) YEAR TO DAY) are not converted.
SQL object names like table and column names cannot be SQL reserved words in PostgreSQL.
Solution:
Table or column names which are PostgreSQL reserved words must be renamed.
PostgreSQL tables are automatically created with a OID column (Object Identifier) of type INTEGER. The behavior is equivalent to INFORMIX ROWID columns.
Solution:
The database automatically converts ROWID keywords to OID for PostgreSQL. So you can execute "SELECT ROWID FROM" and "UPDATE .. WHERE ROWID = ?" statements as with INFORMIX.
Warning: SQLCA.SQLERRD[6] is not supported. All references to SQLCA.SQLERRD[6] must be removed because this variable will not hold the ROWID of the last INSERTed or UPDATEd row when using the PostgreSQL interface.
INFORMIX SERIAL data type and automatic number production:
PostgreSQL SERIAL data type:
PostgreSQL sequences:
Solution:
The INFORMIX SERIAL data type can be emulated with three methods, according to FGLPROFILE settings:
Warning: When using solution 1 (which is the default), the sqlca.sqlerrd[2] register is not set after an INSERT. You must configure FGLPROFILE to use solution 2 or 3 when you need this feature.
When using solution 2 or 3, the triggers can be created manually during the application database installation procedure, or automatically from a BDL program: When a BDL program executes a CREATE [TEMP] TABLE with a SERIAL column, the database interface automatically converts the SERIAL data type to INTEGER and dynamically creates the triggers. However, when using solution 2, the table SERIALREG must be created before the triggers. The serial production is based on the SERIALREG table which registers the last generated number for each table. If you delete rows of this table, sequences will restart at 1 and you will get unexpected data.
If you plan to use the second method, you must create the SERIALREG table as follows:
CREATE TABLE SERIALREG (
TABLENAME VARCHAR(50) NOT NULL,
LASTSERIAL INTEGER NOT NULL,
PRIMARY KEY ( TABLENAME )
)
Warning: This table must exist in the database before creating the serial triggers.
In database creation scripts, all SERIAL[(n)] data types must be converted to INTEGER data types and you must create one trigger for each table. To know how to write those triggers, you can create a small Genero program that creates a table with a SERIAL column. Set the FGLSQLDEBUG environment variable and run the program. The debug output will show you the native trigger creation command.
Warning: With PostgreSQL,
INSERT statements using NULL for the SERIAL column will produce a new serial value, not a NULL like INFORMIX does:
INSERT INTO tab (col1,col2) VALUES (NULL,'data')
This behavior is mandatory in order to support INSERT statements which do not use the serial column:
INSERT INTO tab (col2) VALUES ('data')
Check if your application uses tables with a SERIAL column that can contain a NULL value.
In INFORMIX SQL, outer tables are defined in the FROM clause with the OUTER keyword:
SELECT ... FROM a, OUTER(b) WHERE a.key = b.akeySELECT ... FROM a, OUTER(b,OUTER(c)) WHERE a.key = b.akey AND b.key1 = c.bkey1 AND b.key2 = c.bkey2
PostgreSQL supports the ANSI outer join syntax:
SELECT ... FROM cust LEFT OUTER JOIN order ON cust.key = order.custnoSELECT ... FROM cust LEFT OUTER JOIN order LEFT OUTER JOIN item ON order.key = item.ordno ON cust.key = order.custno WHERE order.cdate > current date
See the PostgreSQL reference for a complete description of the syntax.
Solution:
The PostgreSQL interface can convert most INFORMIX OUTER specifications to ANSI outer joins.
Prerequisites:
Restrictions:
Remarks:
Most BDL applications use only one database entity (in the meaning of INFORMIX). But the same BDL application can connect to different occurrences of the same database schema, allowing several users to connect to those different databases.
Like INFORMIX servers, PostgreSQL can handle multiple database entities. Tables created by a user can be accessed without the owner prefix by other users as long as they have access privileges to these tables.
Solution:
Create a PostgreSQL database for each INFORMIX database.
Data consistency involves readers which want to access data currently modified by writers and concurrency data access involves several writers accessing the same data for modification. Locking granularity defines the amount of data concerned when a lock is set (row, page, table, ...).
INFORMIX
INFORMIX uses a locking mechanism to handle data consistency and concurrency. When a process changes database information with UPDATE, INSERT or DELETE, an exclusive lock is set on the touched rows. The lock remains active until the end of the transaction. Statements performed outside a transaction are treated as a transaction containing a single operation and therefore release the locks immediately after execution. SELECT statements can set shared locks according to the isolation level. In case of locking conflicts (for example, when two processes want to acquire an exclusive lock on the same row for modification or when a writer is trying to modify data protected by a shared lock), the behavior of a process can be changed by setting the lock wait mode.
Control:
Defaults:
PostgreSQL
When data is modified, exclusive locks are set and held until the end of the transaction. For data consistency, PostgreSQL uses a multi-version consistency model: A copy of the original row is kept for readers before performing writer modifications. Readers do not have to wait for writers as in INFORMIX. The simplest way to think of PostgreSQL implementation of read consistency is to imagine each user operating a private copy of the database, hence the multi-version consistency model. The lock wait mode cannot be changed as in INFORMIX. Locks are set at the row level in PostgreSQL and this cannot be changed.
Control:
Defaults:
The main difference between INFORMIX and PostgreSQL is that readers do not have to wait for writers in PostgreSQL.
Solution:
For portability, it is recommended that you work with INFORMIX in the read committed isolation level, make processes wait for each other (lock mode wait), and create tables with the "lock mode row" option.
See INFORMIX and PostgreSQL documentation for more details about data consistency, concurrency and locking mechanisms.
A lot of BDL programs use pessimistic locking in order to avoid several users editing the same rows at the same time.
DECLARE cc CURSOR FOR
SELECT ... FROM tab WHERE ... FOR UPDATE
OPEN cc
FETCH cc <-- lock is acquired
...
CLOSE cc <-- lock is released
In both INFORMIX and PostgreSQL, locks are released when closing the cursor or when the transaction ends.
PostgreSQL locking granularity is at the row level.
To control the behavior of the program when locking rows, INFORMIX provides a specific instruction to set the wait mode:
SET LOCK MODE TO { WAIT | NOT WAIT | WAIT seconds }
The default mode is NOT WAIT. This as an INFORMIX specific SQL statement.
Warning: PostgreSQL has no equivalent for "SET LOCK MODE TO NOT WAIT".
Solution:
Warning: The database interface is based on an emulation of an Informix engine using transaction logging. Therefore, opening a SELECT ... FOR UPDATE cursor declared outside a transaction will raise an SQL error -255 (not in transaction).
You must review the program logic if you use pessimistic locking because it is based on the NOT WAIT mode which is not supported by PostgreSQL.
INFORMIX and PostgreSQL handle transactions in a similar manner.
INFORMIX native mode (non ANSI):
PostgreSQL:
Transactions in stored procedures: avoid using transactions in stored procedures to allow the client applications to handle transactions, according to the transaction model.
Warning: The main difference between Informix and PostgreSQL resides in the fact that PostgreSQL cancels the whole transaction if an SQL error occurs in one of the statements executed inside the transaction. The following code example illustrates this difference:
CREATE TABLE tab1 ( k INT PRIMARY KEY, c CHAR(10) )
WHENEVER ERROR CONTINUE
BEGIN WORK
INSERT INTO tab1 ( 1, 'abc' )
INSERT INTO tab1 ( 1, 'abc' ) -- PK constraint violation = SQL
Error
COMMIT WORK
With Informix, this code will leave the table with one row inside, since the first INSERT statement succeeded. With PostgreSQL, the table will remain empty after executing this piece of code, because the server will rollback the whole transaction.
Solution:
INFORMIX transaction handling commands are automatically converted to PostgreSQL instructions to start, validate or cancel transactions.
Regarding the transaction control instructions, the BDL applications do not have to be modified in order to work with PostgreSQL.
Warning: You must review the SQL statements inside BEGIN WORK / COMMIT WORK instruction and check if these can raise and SQL error. With PostgreSQL, the whole transaction will be aborted. The code example shown above could for example be converted to this:
CREATE TABLE tab1 ( k INT PRIMARY KEY, c CHAR(10) )
WHENEVER ERROR CONTINUE
BEGIN WORK
INSERT INTO tab1 ( 1, 'abc' )
IF SQLCA.SQLCODE < 0 THEN ROLLBACK WORK GOTO _END_ END IF
INSERT INTO tab1 ( 1, 'abc' ) -- PK constraint violation = SQL
Error
IF SQLCA.SQLCODE < 0 THEN ROLLBACK WORK GOTO _END_ END IF
COMMIT WORK
LABEL _END_:
...
The PostgreSQL connector is implemented with the PostgreSQL libpq API. This library does not provide a way to send SQL statements to the database server during the BDL PREPARE instruction, like the INFORMIX interface. The statement is sent to the server only when opening the cursors or when executing the statement.
Therefore, when preparing an SQL statement with the BDL PREPARE instruction, no SQL errors can be returned if the statement has syntax errors or if a column or a table name does not exist in the database.
However, an SQL error will occur after the OPEN or EXECUTE instructions.
Solution:
Check that your BDL programs do not test STATUS or SQLCA.SQLCODE variable just after PREPARE instructions.
Change the program logic in order to handle the SQL errors when opening the cursors (OPEN) or when executing SQL statements (EXECUTE).
As in INFORMIX, PostgreSQL provides the CHAR and VARCHAR data types to store character data.
INFORMIX CHAR type can store up to 32767 characters and the VARCHAR data type is limited to 255 characters.
Since PostgreSQL CHAR and VARCHAR have a size limit of 1GB.
Solution:
The database interface supports character string variables in SQL statements for input (BDL USING) and output (BDL INTO).
Warning: PostgreSQL raises an error if the LENGTH() parameter is NULL. INFORMIX returns zero instead.
Solution:
The PostgreSQL database interface cannot simulate the behavior of the INFORMIX LENGTH() SQL function.
Review the program logic and make sure you do not pass NULL values to the LENGTH() SQL function.
Constraint naming syntax:
Both INFORMIX and PostgreSQL support primary key, unique, foreign key, default and check constraints, but the constraint naming syntax is different. PostgreSQL expects the "CONSTRAINT" keyword before the constraint specification and INFORMIX expects it after.
UNIQUE constraint example:
| INFORMIX | PostgreSQL |
| CREATE TABLE scott.emp ( ... empcode CHAR(10) UNIQUE [CONSTRAINT pk_emp], ... |
CREATE TABLE scott.emp ( ... empcode CHAR(10) [CONSTRAINT pk_emp] UNIQUE, ... |
Unique constraints:
Warning: When using a unique constraint, INFORMIX allows only one row with a NULL value, while PostgreSQL allows several rows with NULL!
Solution:
The database interface does not convert constraint naming expressions when creating tables from BDL programs. Review the database creation scripts to adapt the constraint naming clauses for PostgreSQL.
INFORMIX and PostgreSQL provide triggers with similar features, but the trigger creation syntax and the programming languages are totally different.
Solution:
INFORMIX triggers must be converted to PostgreSQL triggers "by hand".
Both INFORMIX and PostgreSQL support stored procedures, but the programming languages are totally different. With PostgreSQL you must create the stored procedure language before writing triggers or stored procedures.
Solution:
INFORMIX stored procedures must be converted to PostgreSQL manually.
INFORMIX users are defined at the operating system level, they must be members of the 'informix' group, and the database administrator must grant CONNECT, RESOURCE or DBA privileges to those users.
PostgreSQL users must be registered in the database. They are created by the createuser utility:
$ createuser --username=<username> --password
Solution:
According to the application logic (is it a multi-user application ?), you have to create one or several PostgreSQL users.
INFORMIX and PostgreSQL user privileges management are quite similar.
PostgreSQL provides user groups to grant or revoke permissions to more than one user at the same time.
INFORMIX temporary tables are created through the CREATE TEMP TABLE DDL instruction or through a SELECT ... INTO TEMP statement. Temporary tables are automatically dropped when the SQL session ends, but they can be dropped with the DROP TABLE command. There is no name conflict when several users create temporary tables with the same name.
INFORMIX allows you to create indexes on temporary tables. No name conflict occurs when several users create an index on a temporary table by using the same index identifier.
PostgreSQL support temporary tables as INFORMIX does, with a little syntax difference in the SELECT INTO TEMP instruction.
Solution:
Temporary tables are well supported with native PostgreSQL temp tables.
INFORMIX SQL statements can use subscripts on columns defined with the character data type:
SELECT ... FROM tab1 WHERE col1[2,3] = 'RO'
SELECT ... FROM tab1 WHERE col1[10]
= 'R' -- Same as col1[10,10]
UPDATE tab1 SET col1[2,3]= 'RO' WHERE ...
SELECT ... FROM tab1 ORDER BY col1[1,3]
.. while PostgreSQL provides the SUBSTR( ) function, to extract a substring from a string expression:
SELECT .... FROM tab1 WHERE SUBSTRING(col1 from 2 for 2) = 'RO'
SELECT SUBSTRING('Some text' from 6 for 3) ... -- Gives 'tex'
Solution:
You must replace all Informix col[x,y] expressions by SUBSTRING( col from x for (y-x+1) ).
Warning: In UPDATE
instructions, setting column values through subscripts will produce an error with PostgreSQL :
UPDATE tab1 SET col1[2,3] = 'RO' WHERE ...
is converted to:
UPDATE tab1 SET SUBSTRING(col1 from 2 for (3-2+1)) = 'RO' WHERE ...
Warning: Column
subscripts in ORDER BY expressions are also converted and produce an error with PostgreSQL:
SELECT ... FROM tab1 ORDER BY col1[1,3]
is converted to:
SELECT ... FROM tab1 ORDER BY SUBSTRING(col1 from 1 for(3-1+1))
INFORMIX uses the following form to identify an SQL object :
[database[@dbservername]:][{owner|"owner"}.]identifier
With PostgreSQL, an object name takes the following form:
[owner.]identifier
Solution:
Check for single or double quoted table or column names in your source and remove them.
The ANSI string delimiter character is the single quote ( 'string'). Double quotes are used to delimit database object names ("object-name").
Example: WHERE "tabname"."colname" = 'a string value'
INFORMIX allows double quotes as string delimiters, but PostgreSQL doesn't. This is important since many BDL programs use that character to delimit the strings in SQL commands.
Note: This problem concerns only double quotes within SQL statements. Double quotes used in pure BDL string expressions are not subject to SQL compatibility problems.
Solution:
The PostgreSQL database interface can automatically replace all double quotes by single quotes.
Escaped string delimiters can be used inside strings like following:
'This is a single quote: '''
'This is a single quote : \''
"This is a double quote : """
"This is a double quote : \""
Warning: Database object names cannot be delimited by double quotes because the database interface cannot determine the difference between a database object name and a quoted string!
For example, if the program executes the SQL statement:
WHERE "tabname"."colname" = "a string value"
replacing all double quotes by single quotes would produce:
WHERE 'tabname'.'colname' = 'a string value'
This would produce an error since
'tabname'.'colname' is not allowed by PostgreSQL.
Although double quotes are replaced automatically in SQL statements, you should use only single quotes to enforce portability.
INFORMIX supports several data types to store numbers:
| INFORMIX Data Type | Description |
| SMALLINT | 16 bit integer ( -2^15 to 2^15 ) |
| INT/INTEGER | 32 bit integer ( -2^31 to 2^31 ) |
| DEC/DECIMAL(p) | Floating-point decimal number |
| DEC/DECIMAL(p,s) | Fixed-point decimal number |
| MONEY | Equivalent to DECIMAL(16,2) |
| MONEY(p) | Equivalent to DECIMAL(p,2) |
| MONEY(p,s) | Equivalent to DECIMAL(p,s) |
| REAL/SMALLFLOAT | approx floating point (C float) |
| DOUBLE PREC./FLOAT | approx floating point (C double) |
Solution:
PostgreSQL supports the following data types to store numbers:
| PostgreSQL data type | Description |
| NUMERIC(p,s) | Decimals (no limit) |
| DECIMAL(p,s) | Decimals (8000 digits) |
| FLOAT4 | 4 bytes variable precision |
| FLOAT8 | 8 bytes variable precision |
| INT2 | 2 bytes integer |
| INT4 | 4 bytes integer |
| INT8 | 8 bytes integer |
ANSI types like SMALLINT, INTEGER, FLOAT are supported by PostgreSQL as aliases to INT2, INT4 and FLOAT8 native types.
PostgreSQL expects the ANSI notation for column aliases :
SELECT col1 AS col1_alias FROM ...
INFORMIX supports the ANSI notation.
Solution:
Warning: The database interface cannot convert INFORMIX alias specification to the ANSI notation.
Review your programs and replace the INFORMIX notation with the ANSI form.
INFORMIX supports MATCHES and LIKE in SQL statements. PostgreSQL supports the LIKE statement as in INFORMIX, plus the ~ operators that are similar but different from the INFORMIX MATCHES operator.
MATCHES allows brackets to specify a set of matching characters at a given position :
( col MATCHES '[Pp]aris' ).
( col MATCHES '[0-9][a-z]*' ).
In this case, the LIKE statement has not equivalent feature.
The following substitutions must be made to convert a MATCHES condition to a LIKE condition :
PostgreSQL ~ operator expects regular expressions as follows:
( col ~ 'a.*' )
Solution:
Warning: SQL statements using MATCHES expressions must be reviewed in order to use LIKE expressions.
See also: MATCHES operator in SQL Programming.
The BDL compiler supports several INFORMIX specific SQL statements that have no meaning when using PostgreSQL.
Solution:
Review your BDL source and remove all static SQL statements which are INFORMIX specific.
INFORMIX supports insert cursors. An "insert cursor" is a special BDL cursor declared with an INSERT statement instead of a SELECT statement. When this kind of cursor is open, you can use the PUT instruction to add rows and the FLUSH instruction to insert the records into the database.
For INFORMIX database with transactions, OPEN, PUT and FLUSH instructions must be executed within a transaction.
PostgreSQL does not support insert cursors.
Solution:
Insert cursors are emulated by the PostgreSQL database interface.
Both INFORMIX and PostgreSQL Server provide special data types to store very large texts or images, but the names are different:
| INFORMIX Data Type | PostgreSQL Data Type |
| TEXT | TEXT |
| BYTE | BYTEA |
Solution:
Very large data types are not supported yet by the PostgreSQL database interface.
INFORMIX closes opened cursors automatically when a transaction ends unless the WITH HOLD option is used in the DECLARE instruction. In PostgreSQL, opened cursors using SELECT statements without a FOR UPDATE clause are not closed when a transaction ends. Actually, all PostgreSQL cursors are 'WITH HOLD' cursors unless the FOR UPDATE clause is used in the SELECT statement.
Warning: Cursors declared FOR UPDATE and using the WITH HOLD option cannot be supported with PostgreSQL because FOR UPDATE cursors are automatically closed by PostgreSQL when the transaction ends.
Solution:
BDL cursors that are not declared "WITH HOLD" are automatically closed by the database interface when a COMMIT WORK or ROLLBACK WORK is performed.
Warning: Since PostgreSQL automatically closes FOR UPDATE cursors when the transaction ends, opening cursors declared FOR UPDATE and WITH HOLD option results in an SQL error that does not normally appear with INFORMIX, in the same conditions. Review the program logic in order to find another way to set locks.
INFORMIX allows positioned UPDATEs and DELETEs with the "WHERE CURRENT OF <cursor>" clause, if the cursor has been DECLARED with a SELECT ... FOR UPDATE statement.
PGS 8.1 and 8.2: UPDATE/DELETE ... WHERE CURRENT OF <cursor> is not supported by PostgreSQL. However, you can use the OID column to do positioned updates/deletes.
Since PGS 8.3: UPDATE/DELETE ... WHERE CURRENT OF <cursor> is supported by PostgreSQL with server-side cursors created with a DECLARE statement.
Solution:
With PostgreSQL 8.1 (dbmpgs81x) and 8.2 (dbmpgs82x):
UPDATE/DELETE ... WHERE CURRENT OF instructions are managed by the PostgreSQL database interface. The PostgreSQL database interface replaces "WHERE CURRENT OF <cursor>" by "WHERE OID = ?" and sets the value of the Object Identifier returned by the last FETCH done with the given cursor..
Warning PGS 8.1 and 8.2: Starting with PostgreSQL version 8.1, user tables do not get the oid column by default. You must set the default_with_oid configuration parameter in the postgresql.conf file.
With PostgreSQL 8.3 (dbmpgs83x) and higher:
UPDATE/DELETE ... WHERE CURRENT OF instructions are just executed as is. Since SELECT FOR UPDATE statements are now executed with a server cursor by using a DECLARE PostgreSQL statement, native positioned update/delete takes place.
As in INFORMIX, PostgreSQL provides system catalog tables (actually, system views). But the table names and their structure are quite different.
Solution:
Warning: No automatic conversion of INFORMIX system tables is provided by the database interface.
INFORMIX allows a specific syntax for UPDATE statements:
UPDATE table SET ( <col-list> ) = ( <val-list> )
or
UPDATE table SET table.* = myrecord.*
UPDATE table SET * = myrecord.*
Solution:
Static UPDATE statements using the above syntax are converted by the compiler to the standard form:
UPDATE table SET column=value [,...]
INFORMIX provides the LENGTH() function:
SELECT LENGTH("aaa"), LENGTH(col1) FROM table
PostgreSQL has a equivalent function with the same name, but there is some difference:
Informix does not count the trailing blanks neither for CHAR not for VARCHAR expressions, while PostgreSQL counts the trailing blanks.
With the PostgreSQL LENGTH function, when using a CHAR column, values are always blank padded, so the function returns the size of the CHAR column. When using a VARCHAR column, trailing blanks are significant, and the function returns the number of characters, including trailing blanks.
Solution:
You must check if the trailing blanks are significant when using the LENGTH() function.
If you want to count the number of character by ignoring the trailing blanks, you must use the RTRIM() function:
INFORMIX INTERVAL data type stores a value that represents a span of time. INTERVAL types are divided into two classes : year-month intervals and day-time intervals.
PostgreSQL provides an INTERVAL data type, but it is totally different from the INFORMIX INTERVAL type. For example, you specify a INTERVAL literal as follows :
25 years 2 months 23 days
Solution:
Warning: The INTERVAL data type is not well supported because the database server has no equivalent native data type. However, you can store into and retrieve from CHAR columns BDL INTERVAL values.
An attempt should be made to preserve as much of the storage information as possible when converting from INFORMIX to PostgreSQL. Most important storage decisions made for INFORMIX database objects (like initial sizes and physical placement) can be reused for the PostgreSQL database.
Storage concepts are quite similar in INFORMIX and in PostgreSQL, but the names are different.
INFORMIX: NCHAR & NVARCHAR
PostgreSQL:
Solution:
Warning: National character data types are not supported yet.
INFORMIX provides two SQL instructions to export / import data from / into a database table: The UNLOAD instruction copies rows from a database table into a text file and the LOAD instructions insert rows from a text file into a database table.
PostgreSQL does not provide LOAD and UNLOAD instructions, but provides external tools like SQL*Plus and SQL*Loader.
Solution:
LOAD and UNLOAD instructions are supported.
Warning: There is a
difference when using PostgreSQL DATE columns:
DATE columns created in the PostgreSQL database are similar to INFORMIX
DATETIME YEAR TO SECOND columns. In LOAD and UNLOAD, all PostgreSQL DATE columns are treated
as INFORMIX DATETIME YEAR TO SECOND columns and thus will be unloaded with the
"YYYY-MM-DD hh:mm:ss" format.
The same problem appears for INFORMIX INTEGER and SMALLINT values which are stored in
a PostgreSQL database as NUMBER(?) columns. Those values will be unloaded as INFORMIX
DECIMAL(10) and DECIMAL(5) values, that is, with a trailing dot-zero ".0".
Warning: When using an INFORMIX database, simple dates are unloaded with the DBDATE format (ex: "23/12/1998"). Therefore, unloading from an INFORMIX database for loading into an PostgreSQL database is not supported.
Warning: UNLOAD instructions based on SELECT statements using date expressions as "WHERE ?-datecol>?" are not supported because of database server limitations. A syntax error would be raised in this case.
With Informix, it is possible to interrupt a long running query if the SQL INTERRUPT ON option is set by the Genero program. The database server returns SQLCODE -213, which can be trapped to detect a user interruption.
MAIN
DEFINE n INTEGER
DEFER INTERRUPT
OPTIONS SQL INTERRUPT ON
DATABASE test1
WHENEVER ERROR CONTINUE
-- Start long query (self join takes time)
-- From now on, user can hit CTRL-C in TUI mode
to stop the query
SELECT COUNT(*) INTO n FROM customers a,
customers b
WHERE a.cust_id
<> b.cust_id
IF SQLCA.SQLCODE == -213 THEN
DISPLAY "Statement was
interrupted by user..."
EXIT PROGRAM 1
END IF
WHENEVER ERROR STOP
...
END MAIN
PostgreSQL supports SQL Interruption in a similar way as Informix. The db client must issue an PQcancel() libPQ call to interrupt a query.
Solution:
The PostgreSQL database driver supports SQL interruption and converts the SQLSTATE code 57014 to the Informix error code -213.
| INFORMIX Data Types | PostgreSQL Data Types |
| CHAR(n) | CHAR(n) |
| VARCHAR(n) | VARCHAR(n) |
| INTEGER | INT4 |
| SMALLINT | INT2 |
| FLOAT[(n)] | FLOAT4 |
| SMALLFLOAT | FLOAT8 |
| DECIMAL(p,s) | NUMERIC(p,s) |
| MONEY(p,s) | NUMERIC(p,s) |
| DATE | DATE |
| DATETIME HOUR TO SECOND | TIME(0) WITHOUT TIME ZONE |
| DATETIME YEAR TO FRACTION(p) | TIMESTAMP(p) WITHOUT TIME ZONE |
| INTERVAL q1 TO q2 | N/A |