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

However, Netezza® does not have a SERIAL data type. Version 6 of the database supports SEQUENCEs, but not triggers. The lack of triggers support makes it impossible to emulate Informix SERIALs.

Solution

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