The SERIAL
data type is a column data type that, on insert, generates a default integer from the timestamp and ID of the node executing the insert. This combination is likely to be globally unique except in extreme cases (see this example for more details). Also, because value generation does not require talking to other nodes, it is much faster than sequentially auto-incrementing a value, which requires distributed coordination.
UUID
data type with the gen_random_uuid()
function as the default value, which generates 128-bit values (much larger than SERIAL
's 64-bit) and scatters them across all of a table's underlying key-value ranges, ensuring that multiple nodes share in the load. See Create a table with auto-generated unique row IDs for more details.Aliases
The SERIAL
type is equivalent to INT DEFAULT unique_rowid()
.
In CockroachDB, the following are aliases for SERIAL
:
SMALLSERIAL
BIGSERIAL
Syntax
Any INT
value is a valid SERIAL
value; in particular constant SERIAL
values can be expressed using numeric literals.
Size
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.
Supported Casting & Conversion
Values of type SERIAL
can be converted to other types like any INT
values.