SERIAL data types

Informix® supports the SERIAL, SERIAL8 and BIGSERIAL data types to produce automatic integer sequences. SERIAL is based on INTEGER (32 bit), while SERIAL8 and BIGSERIAL can store 64 bit integers:

Informix allows you to insert rows with a value different from zero for a serial column. Using an explicit value will automatically increment the internal serial counter, to avoid conflicts with future INSERT statements that are using a zero value:

CREATE TABLE tab ( k SERIAL); -- internal counter = 0
INSERT INTO tab VALUES ( 0 ); -- internal counter = 1
INSERT INTO tab VALUES ( 10 ); -- internal counter = 10
INSERT INTO tab VALUES ( 0 ); -- internal counter = 11
DELETE FROM tab; -- internal counter = 11
INSERT INTO tab VALUES ( 0 ); -- internal counter = 12

PostgreSQL SERIAL data type:

PostgreSQL sequences:

Solution

The Informix SERIAL data type can be emulated with three different methods, based on the following FGLPROFILE setting:

dbi.database.dbname.ifxemul.datatype.serial.emulation

This entry can have the following values: "native", "regtable" and "trigseq".

Using the native serial emulation

The "native" mode is the default serial emulation mode, using the native PostgreSQL SERIAL data type. In this mode, the original type name will be left untouched by the SQL Translator and you will get the behavior of the PostgreSQL SERIAL column type, based on sequences.

Note: INSERT statements cannot use the serial column, even with a value zero. When using a NULL value, PostgreSQL will report an non-null constraint error. Therefore, the serial column must be omitted from the INSERT statement.

The sqlca.sqlerrd[2] register is not set after an INSERT when using a PostgreSQL version prior to version 8.3.

See also the PostgreSQL documentation for more details about the native SERIAL type.

Using the regtable serial emulation

With the "regtable" mode, the SERIAL data type is emulated with a PostgreSQL INTEGER data type and INSERT triggers using the table SERIALREG which is dedicated to sequence production. After an insert, sqlca.sqlerrd[2] register holds the last generated serial value. BIGSERIAL and SERIAL8 types can be converted to BIGINT in PostgreSQL, but the sqlca.sqlerrd[2] register cannot be used since it is defined as an INTEGER type.

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.

You must create the SERIALREG table as follows:

CREATE TABLE SERIALREG (
   TABLENAME VARCHAR(50) NOT NULL,
   LASTSERIAL DECIMAL(20,0) NOT NULL,
   PRIMARY KEY ( TABLENAME )
)
Important: The SERIALREG table 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.

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.

With this emulation mode, INSERT statements using NULL for the SERIAL column will produce a new serial value:

INSERT INTO tab (col1,col2) VALUES (NULL,'data')

This behavior is mandatory in order to support INSERT statements that 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. Consider removing the serial column from the INSERT statements.

Using the trigseq serial emulation

With "trigseq", the SERIAL data type is emulated with a PostgreSQL INTEGER data type and INSERT triggers using a sequence tablename_seq. After an insert, sqlca.sqlerrd[2] register holds the last generated serial value.

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.

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.

With this emulation mode, INSERT statements using NULL for the SERIAL column will produce a new serial value:

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. Consider removing the serial column from the INSERT statements.

Issues common to all serial emulation modes

Since sqlca.sqlerrd[2] is defined as an INTEGER, it cannot hold values from BIGSERIAL (BIGINT) auto incremented columns. If you are using BIGSERIAL columns, you must query the sequence pseudo-column CURRVAL() or fetch the LASTSERIAL column from the SERIALREG table if used.

For SQL portability, INSERT statements should be reviewed to remove the SERIAL column from the list.

For example, the following statement:

INSERT INTO tab (col1,col2) VALUES ( 0 , p_value )

can be converted to:

INSERT INTO tab (col2) VALUES (p_value)

Static SQL INSERT using records defined from the schema file must also be reviewed:

DEFINE rec LIKE tab.*
INSERT INTO tab VALUES ( rec.*) -- will use the serial column

can be converted to:

INSERT INTO tab VALUES rec.* -- without braces, serial column is removed
Important: When using the Static SQL INSERT and UPDATE syntax using record.* without braces, make sure that you database schema files contain information about serials: This information can be lost when extracting the schema from a PostgreSQL database which does not use native serial emulation. See Database Schema for more details about the serial flag in column type encoding (data type code must be 6)