SERIAL and BIGSERIAL data types


Informix supports the SERIAL, BIGSERIAL data types to produce automatic integer sequences:
  • SERIAL can produce 32 bit integers (INTEGER)
  • BIGSERIAL can produce 64 bit integers (BIGINT)
  • SERIAL8 is a synonym for BIGSERIAL
Steps to use serials with Informix:
  1. Create the table with a column using SERIAL, or BIGSERIAL.
  2. To generate a new serial, no value or a zero value is specified in the INSERT statement:
    INSERT INTO tab1 ( c ) VALUES ( 'aa' )
    INSERT INTO tab1 ( k, c ) VALUES ( 0, 'aa' )
  3. After INSERT, the new value of a SERIAL column is provided in sqlca.sqlerrd[2], while the new value of a BIGSERIAL value must be fetched with a SELECT dbinfo('bigserial') query.

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:

SQL statement                                               Internal serial counter
CREATE TABLE tab ( pkey SERIAL, name VARCHAR(50) );                    0
INSERT INTO tab VALUES (  0, 'aaa' );                                  1
INSERT INTO tab VALUES ( 10, 'bbb' );                                 10
INSERT INTO tab VALUES (  0, 'ccc' );                                 11
DELETE FROM tab;                                                      11
INSERT INTO tab VALUES (  0, 'ddd' );                                 12


Netezza does not have a SERIAL data type.

Netezza Version 6 supports sequences, but not triggers.


The lack of trigger support in Netezza makes it impossible to emulate Informix SERIALs.


For best SQL portability when using different types of databases, consider using sequences as described in Solution 3: Use native SEQUENCE database objects.

If you are using Informix SERIAL or BIGSERIAL columns, you must review the application logic and database schema to replace these columns with INTEGER/BIGINT columns, and generate the new keys from a SEQUENCE, as described in the SQL Programming page.