The 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
a 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 NULL
.
SERIAL
is provided only for compatibility with PostgreSQL. New applications should use real data types and a suitable DEFAULT
expression.
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.
Behavior
The keyword SERIAL
is recognized in CREATE TABLE
and is
automatically translated to a real data type and a DEFAULT
expression using unique_rowid()
during table
creation.
The result of this translation is then used internally by CockroachDB,
and can be observed using SHOW CREATE TABLE
.
The chosen 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 unique_rowid()
specifically must use the full explicit syntax INT DEFAULT
unique_rowid()
and avoid SERIAL
altogether.
For compatibility with PostgreSQL, CockroachDB recognizes the following keywords as aliases to SERIAL
:
SERIAL2
SERIAL4
SERIAL8
SMALLSERIAL
BIGSERIAL
SERIAL2
and 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
INSERT
or UPSERT
operation.
This behavior is statistically likely to be globally unique except in
extreme cases (see this FAQ
entry
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.
Examples
Use 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);
The SHOW COLUMNS
statement shows that the SERIAL
type is just an alias for INT
with 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 a
.
> 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
SERIAL
column.> 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
a
by one, the first commited insert got the value2
, and the second commited insert got the value4
. 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.