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.
We believe this data type is a better solution than PostgreSQL's SERIAL and MySQL's AUTO_INCREMENT types, both of which auto-increment integers but not necessarily in a strictly sequential fashion (see the Auto-Incrementing Is Not Always Sequential example below). However, if you find that this feature is incompatible with your application, please open an
issue.
Aliases
The SERIAL type is equivalent to INT DEFAULT unique_rowid().
In CockroachDB, the following are aliases for SERIAL:
SMALLSERIALBIGSERIAL
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
SERIALcolumn.> 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 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.
See Also
Was this helpful?
