SERIAL pseudo data type is a keyword that can
be used in lieu of a real data type when defining table columns. It
is approximately equivalent to using an integer type with
DEFAULT expression that generates different
values every time it is evaluated. This default expression in turn
ensures that inserts that do not specify this column will receive an
automatically generated value instead of
SERIAL is provided only for compatibility with PostgreSQL. New applications should use real data types and a suitable
In most cases, we recommend using the
UUID data type with the
gen_random_uuid() function as the default value, which generates 128-bit values (larger than
SERIAL's maximum of 64 bits) and more uniformly scatters them across all of a table's underlying key-value ranges. UUIDs ensure more effectively that multiple nodes share the insert load when a UUID column is used in an index or primary key.
See this FAQ entry for more details.
SERIAL is recognized in
CREATE TABLE and is
automatically translated to a real data type and a
unique_rowid() during table
The result of this translation is then used internally by CockroachDB,
and can be observed using
SHOW CREATE TABLE.
DEFAULT expression ensures that different values are
automatically generated for the column during row insertion. These
are not guaranteed to increase monotonically, see this section
below for details.
The particular choice of
DEFAULT expression when clients use the
SERIAL keyword is subject to change in future versions of
CockroachDB. Applications that wish to use
specifically must use the full explicit syntax
unique_rowid() and avoid
For compatibility with PostgreSQL, CockroachDB recognizes the following keywords as aliases to
SERIAL4 are the same as
SERIAL and store 8-byte values, not 2- or 4-byte values as their names might suggest.
This behavior is updated in CockroachDB v2.1.
Automatically generated values
The default expression
unique_rowid() produces a 64-bit integer from
the current timestamp and ID of the node executing the
This behavior is statistically likely to be globally unique except in
extreme cases (see this FAQ
for more details).
Also, because value generation using
unique_rowid() does not require
inter-node coordination, its performance scales unimpeded when
multiple SQL clients are writing to the table from different nodes.
SERIAL to Auto-Generate Primary Keys
In this example, we create a table with the
SERIAL column as the primary key so we can auto-generate unique IDs on insert.
> CREATE TABLE serial (a SERIAL PRIMARY KEY, b STRING, c BOOL);
SHOW COLUMNS statement shows that the
SERIAL type is just an alias for
unique_rowid() as the default.
> SHOW COLUMNS FROM serial;
+-------+------------+-------+----------------+ | Field | Type | Null | Default | +-------+------------+-------+----------------+ | a | INT | false | unique_rowid() | | b | STRING | true | NULL | | c | BOOL | true | NULL | +-------+------------+-------+----------------+
When we insert rows without values in column
a and display the new rows, we see that each row has defaulted to a unique value in column
> INSERT INTO serial (b,c) VALUES ('red', true), ('yellow', false), ('pink', true); > INSERT INTO serial (a,b,c) VALUES (123, 'white', false); > SELECT * FROM serial;
+--------------------+--------+-------+ | a | b | c | +--------------------+--------+-------+ | 148656994422095873 | red | true | | 148656994422161409 | yellow | false | | 148656994422194177 | pink | true | | 123 | white | false | +--------------------+--------+-------+
Auto-Incrementing Is Not Always Sequential
It's a common misconception that the auto-incrementing types in PostgreSQL and MySQL generate strictly sequential values. In fact, each insert increases the sequence by one, even when the insert is not commited. This means that auto-incrementing types may leave gaps in a sequence.
To experience this for yourself, run through the following example in PostgreSQL:
Create a table with a
> CREATE TABLE increment (a SERIAL PRIMARY KEY);
Run four transactions for inserting rows.
> BEGIN; INSERT INTO increment DEFAULT VALUES; ROLLBACK; > BEGIN; INSERT INTO increment DEFAULT VALUES; COMMIT; > BEGIN; INSERT INTO increment DEFAULT VALUES; ROLLBACK; > BEGIN; INSERT INTO increment DEFAULT VALUES; COMMIT;
View the rows created.
> SELECT * from increment;
+---+ | a | +---+ | 2 | | 4 | +---+
Since each insert increased the sequence in column
aby one, the first commited insert got the value
2, and the second commited insert got the value
4. As you can see, the values aren't strictly sequential, and the last value doesn't give an accurate count of rows in the table.
In summary, the
SERIAL type in PostgreSQL and CockroachDB, and the
AUTO_INCREMENT type in MySQL, all behave the same in that they do not create strict sequences. CockroachDB will likely create more gaps than these other databases, but will generate these values much faster.