SERIAL and BIGSERIAL data types

Informix®

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®

Netezza does not have a SERIAL data type.

Netezza Version 6 supports sequences, but not triggers.

Solution

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

Note:

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.