Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SQL statementsThis chapter describes the syntax and semantics of SQL statements that are recognized by HCL OneDB™.
INSERT statementUse the INSERT statement to insert one or more new rows into a table or view, or to insert one or more elements into an SQL or collection variable.
VALUES Clause
Inserting Values into Serial Columns

Inserting Values into Serial Columns

You can insert successive numbers, explicit values, or explicit values that reset the value in a SERIAL, BIGSERIAL, or SERIAL8 column:

To insert a consecutive serial value
Specify a zero (0) for the serial column in the INSERT statement. In this case, the database server assigns the next highest value.
To insert an explicit value
Specify the nonzero value after first verifying that it does not duplicate one already in the table. If the serial column is uniquely indexed or has a unique constraint, and your value duplicates one already in the table, an error results. If the value is greater than the current maximum value, you will create a gap in the series.
To create a gap in the series (that is, to reset the serial value)
Specify a positive value that is greater than the current maximum value in the column.

Alternatively, you can use the MODIFY clause of the ALTER TABLE statement to reset the next value of a serial column.

For more information, see Altering the Next Serial Value.

NULL values are not valid in serial columns.

In HCL OneDB™, inserting a serial value into a table that is part of a table hierarchy updates all tables in the hierarchy that contain the serial counter with the value that you insert. You can express this value either as zero (0) for the next highest value, or as a specific positive integer.