SERIAL and BIGSERIAL data types
Informix®
SERIAL
,
BIGSERIAL
data types to produce automatic integer sequences:SERIAL
can produce 32 bit integers (INTEGER
)BIGSERIAL
can produced 64 bit integers (BIGINT
)SERIAL8
is a synonym forBIGSERIAL
- Create the table with a column using
SERIAL
, orBIGSERIAL
. - 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' )
- After
INSERT
, the new value of aSERIAL
column is provided insqlca.sqlerrd[2]
, while the new value of aBIGSERIAL
value must be fetched with aSELECT 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:
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
PostgreSQL provides native SERIAL
and BIGSERIAL
data types
which are similar to Informix serial types. However,
native PostgreSQL serial types behaves differently:
- It is not possible to define a start value directly in the database like with
SERIAL(100)
. However, the next generated serial value can be defined with thesetval('sequence', value, true/false)
SQL function. - It is not possible to specify zero as serial value to get a new serial: The PostgreSQL native
serial types are based on default values, thus you must omit the serial column in the
INSERT
statement. - When you
INSERT
a row with a specific value for the serial column, the underlying sequence will not be incremented. As result, by default, the nextINSERT
that does not specify the serial column may get a new sequence that was already inserted explicitly. In such case, the sequence must be reset with thesetval()
SQL function. - With some old versions of PostgreSQL, when you drop the table you must drop the sequence too.
PostgreSQL sequences:
- The purpose of sequences is to provide unique integer numbers.
- To create a sequence, you must use the
CREATE SEQUENCE
statement:CREATE SEQUENCE tab1_seq START WITH 100;
- To get a new sequence value, you must use the
nextval()
function:INSERT INTO tab1 VALUES ( nextval('tab1_seq'), ... )
- To get the last generated number, PostgreSQL provides the
currval()
function:SELECT currval('tab1_seq')
- To reset a sequence, use the
setval('sequence', value, true/false)
function:SELECT setval('tab1_seq',200,false);
See PostgreSQL documentation for more details about serial types and sequences features in this database engine.
Solution
The Informix SERIAL
and
BIGSERIAL
data types can be emulated with three different methods.
ifxemul.datatype.serial.emulation
FGLPROFILE
parameter:dbi.database.dbname.ifxemul.datatype.serial.emulation = {
"native"|
"regtable"|
"trigseq"}
native
: uses the native PostgreSQL serial data types (SERIAL
orBIGSERIAL
).regtable
: usesINSERT
triggers with theSERIALREG
table.trigseq
: usesINSERT
triggers with sequences.
The default emulation technique is "native
".
INSERT
statements. This is not supported.native
" and
"trigseq
"), if the table with serial column is owned by another user, the current
user must have sufficient privileges to use, query and modify the underlying sequence. To grant
globally permissions on serial sequences in a given schema, use the following SQL
commands:-- For existing sequences
GRANT USAGE, SELECT, UPDATE
ON ALL SEQUENCES IN SCHEMA public
TO user-name;
-- For future created sequences (if needed)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT, UPDATE ON SEQUENCES
TO user-name;
dbi.database.dbname.ifxemul.datatype.serial = {
true|
false}
dbi.database.dbname.ifxemul.datatype.serial8 = {
true|
false}
dbi.database.dbname.ifxemul.datatype.bigserial = {
true|
false}
Disabling automatic serial retrieval for sqlca.sqlerrd[2]
ifxemul.datatype.serial
FGLPROFILE entry to
false:dbi.database.dbname.ifxemul.datatype.serial = false
For Informix compatibility, after an
INSERT
statement, the ODI drivers automatically execute another SQL query (or do a
DB client API call when possible), to get the last generated serial, and fill the
sqlca.sqlerrd[2]
register. This results in some overhead that can be avoided, if
the sqlca.sqlerrd[2]
register is not used by the program.
When serial emulation is required (to create temp tables with a serial column during program
execution), and the sqlca.sqlerrd[2]
register does not need to be filled,
(typically because you use your own method to retrieve the last generated serial), you can set the
ifxemul.datatype.serial.sqlerrd2
FGLPROFILE entry to false. This will avoid the
automatic retrieval of last serial value to fill sqlca.sqlerrd[2]
:
dbi.database.dbname.ifxemul.datatype.serial.sqlerrd2 = false
See also db_get_last_serial().
Using the native
serial emulation
The "native
" mode is the default serial emulation mode, using the native
PostgreSQL SERIAL
or BIGSERIAL
data type. In this mode, the
original type name will be left untouched by the SQL Translator to get the PostgreSQL
SERIAL
/BIGSERIAL
column types, based on sequences and the
DEFAULT
clause.
When creating a table with a serial column using start value, the PostgreSQL ODI driver will
convert the Informix type (SERIAL(100)
) to a PostgreSQL native type, without the
start value (SERIAL
), and after executing the CREATE TABLE
statement, the driver will perform a call to the
setval('sequence-name',start-value,false)
SQL
function, to reset the underlying sequence. As result, the next inserted row will get the serial
value start-value.
INSERT
statements that should generate an automatic serial must not use the
serial column and value as NULL
or zero: When using a NULL
value
explicity, PostgreSQL will report an non-null constraint error. When using zero, PostgreSQL will
insert that value as is without incrementing the serial as Informix does.
When the Informix serial emulation is enabled, the PostgreSQL ODI driver will automatically add a
RETURNING
clause to the INSERT
statements having a serial column.
The purpose of that RETURNING
clause is to fetch the new generate serial value, and
reset the underlying sequence, if a serial value is explicitly provided and is greater than or equal
to the last generate serial. Therefore, INSERT
statements can specify an explicit
value that is greater than zero, and the underlying sequence will be reset. When using the
LOAD
instruction, the sequence for native serial/bigserial columns will also be
automatically reset.
SELECT setval(pg_get_serial_sequence('[schema.]tabname','colname'),
(SELECT MAX(colname) FROM tabname), true)
INSERT
execution, the last generated SERIAL
value can
be found in the sqlca.sqlerrd[2]
register, as an INTEGER
. For
BIGSERIAL
columns, you need to execute the following
query:SELECT currval(pg_get_serial_sequence('[schema.]tabname','colname'));
INSERT
specifies an
explicit value for the serial column), the currval()
function will produce an SQL
error, that will cancel the current transaction. This problem does not occur using serial emulation
and sqlca.sqlerrd[2]
. Without serial emulation, or when using
currval()
to retrieve a BIGSERIAL
value, make sure to call
currval()
only after an INSERT
that does not provide a value for
the serial column.See also the PostgreSQL documentation for more details about the native serial types.
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. Similarly,
BIGSERIAL
is converted to BIGINT
and INSERT
trigger.
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/BIGSERIAL
data types to INTEGER/BIGINT
and
dynamically creates the triggers.
With the "regtable
" emulation, the SERIALREG
table must be
created as follows:
CREATE TABLE SERIALREG (
TABLENAME VARCHAR(50) NOT NULL,
LASTSERIAL DECIMAL(20,0) NOT NULL,
PRIMARY KEY ( TABLENAME )
)
Make sure that other users can read from the SERIALREG
table:GRANT SELECT ON SERIALREG TO PUBLIC
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
data types must be converted
to [
(start-value)]
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.
INSERT
execution, the last generated SERIAL
value can
be found in the sqlca.sqlerrd[2]
register, as an INTEGER
. For
BIGSERIAL
, you need to query the SERIALREG
table after the
INSERT
:SELECT lastserial FROM serialreg WHERE tablename = 'tabname'
INSERT
statements using NULL
for the
serial columns will produce a new serial
value:INSERT INTO tab ( col1, col2 ) VALUES ( NULL, 'data' )
INSERT
statements that do not use
the serial column:INSERT INTO tab (col2) VALUES ('data')
When the Informix serial emulation is enabled, the PostgreSQL ODI driver will automatically add a
RETURNING
clause to the INSERT
statements having a serial column.
The purpose of that RETURNING
clause is to return the new generate serial
value.
Check if your application uses tables with a SERIAL/BIGSERIAL
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
. Similarly, BIGSERIAL
is
converted to BIGINT
and INSERT
trigger.
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/BIGSERIAL
data types to INTEGER/BIGINT
and dynamically
creates the triggers.
In database creation scripts, all
SERIAL
data types must be converted
to [
(start-value)]
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.
INSERT
execution, the last generated SERIAL
value can
be found in the sqlca.sqlerrd[2]
register, as an INTEGER
. For
BIGSERIAL
, you need to execute the following
query:SELECT currval('[schema.]tabname'_seq);
INSERT
specifies an
explicit value for the serial column), the currval()
function will produce an SQL
error, that will cancel the current transaction. This problem does not occur using serial emulation
and sqlca.sqlerrd[2]
. Without serial emulation, or when using
currval()
to retrieve a BIGSERIAL
value, make sure to call
currval()
only after an INSERT
that does not provide a value for
the serial column.trigseq
" emulation mode, INSERT
statements using
NULL
for the serial column will produce a new serial
value:INSERT INTO tab ( col1, col2 ) VALUES ( NULL, 'data' )
INSERT
statements which do not
use the serial column:INSERT INTO tab (col2) VALUES ('data')
When the Informix serial emulation is enabled, the PostgreSQL ODI driver will automatically add a
RETURNING
clause to the INSERT
statements having a serial column.
The purpose of that RETURNING
clause is to return the new generate serial
value.
Check if your application uses tables with a SERIAL/BIGSERIAL
column that can
contain a NULL
value. Consider removing the serial column from the
INSERT
statements.
Notes 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.
INSERT
statements. Mixing or switching between PostgreSQL
schemas is not supported.For SQL portability, it is recommended to review INSERT
statements 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 parentheses, serial column is removed