Serial data types

Serial data types are integers with an additional value-generation mechanism. They are used for auto-increment columns: each new row inserted into the table automatically gets a unique value in such a column (similar to the SERIAL type in PostgreSQL or AUTO_INCREMENT in MySQL).

Usage example

CREATE TABLE users (
    user_id Serial,
    name Utf8,
    email Utf8,
    PRIMARY KEY (user_id)
);

UPSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');
REPLACE INTO users (name, email) VALUES ('John', 'john@example.com');

SELECT * FROM users;

You can supply a value for a Serial column explicitly on insert; the row is then handled like a plain integer column, and the Sequence is not affected:

UPSERT INTO users (user_id, name, email) VALUES (4, 'Peter', 'peter@example.com');

Description

Only columns that participate in a table's primary key may use the Serial type.

Defining this type on a column creates a separate schema object, Sequence, bound to that column and used as a generator of sequence values. This object is private and hidden from the user. The Sequence is destroyed together with the table.

Sequence values start at 1, increment by 1, and are bounded according to the type used.

If the sequence overflows on insert, an error is returned:

Error: Failed to get next val for sequence: /dev/test/users/_serial_column_user_id, status: SCHEME_ERROR
    <main>: Error: sequence [OwnerId: <some>, LocalPathId: <some>] doesn't have any more values available, code: 200503

The next value is allocated by the generator before the row is actually inserted and is considered consumed even if the row is not successfully written (for example, when the transaction rolls back). Therefore the set of values in such a column may have gaps and consist of several disjoint ranges.

For tables with auto-increment columns, the copy, dump, restore, and import/export operations are supported.