SERIAL

On this page

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.

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.

For compatibility with PostgreSQL, CockroachDB recognizes the following keywords as aliases to SERIAL:

• SERIAL2
• SERIAL4
• SERIAL8
• SMALLSERIAL
• BIGSERIAL

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:

1. Create a table with a SERIAL column.

   > CREATE TABLE increment (a SERIAL PRIMARY KEY);
   

2. 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;
   

3. 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 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.

See also

• FAQ: How do I auto-generate unique row IDs in CockroachDB?
• Data Types

Was this helpful?