Columns that have a SERIAL data type are auto-incremented.
YugabyteDB supports SMALLSERIAL, SERIAL, and BIGSERIAL which are short notation for sequences of SMALLINT, INTEGER, and BIGINT, respectively.
Example:
yugabyte=> CREATE TABLE tab1 (id SERIAL, c1 VARCHAR);
CREATE TABLE
yugabyte=> \d tab1;
Table "public.tab1"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+----------------------------------
id | integer | | not null | nextval('tab1_id_seq'::regclass)
c1 | character varying | | |
Note that a Sequence was automatically created as the default value for the SERIAL column. This sequence is used to auto-increment the integer values in the column.
yugabyte=> INSERT INTO tab1 (c1) SELECT 'A';
INSERT 0 1
yugabyte=> INSERT INTO tab1 (c1) SELECT 'B';
INSERT 0 1
yugabyte=> INSERT INTO tab1 (c1) SELECT 'C';
INSERT 0 1
yugabyte=> SELECT * FROM tab1 ORDER BY id;
id | c1
----+----
1 | A
2 | B
3 | C
(3 rows)
You can easily override the default value of a SERIAL column by simply inserting your own integer value.
yugabyte=> INSERT INTO tab1 (id, c1) SELECT 4, 'D';
INSERT 0 1
yugabyte=> INSERT INTO tab1 SELECT 5, 'E';
INSERT 0 1
yugabyte=> SELECT * FROM tab1 ORDER BY id, c1;
id | c1
----+----
1 | A
2 | B
3 | C
4 | D
5 | E
(5 rows)
Be careful with the override feature on columns defined with a PRIMARY or UNIQUE KEY constraint! You might run into a duplicate key error…
yugabyte=> DROP TABLE tab1;
DROP TABLE
yugabyte=> CREATE TABLE tab1 (id SERIAL PRIMARY KEY, c1 VARCHAR);
CREATE TABLE
yugabyte=> INSERT INTO tab1 (c1) SELECT 'A';
INSERT 0 1
yugabyte=> INSERT INTO tab1 (c1) SELECT 'B';
INSERT 0 1
yugabyte=> INSERT INTO tab1 (c1) SELECT 'C';
INSERT 0 1
yugabyte=> SELECT * FROM tab1 ORDER BY id, c1;
id | c1
----+----
1 | A
2 | B
3 | C
(3 rows)
yugabyte=> INSERT INTO tab1 SELECT 4, 'D';
INSERT 0 1
yugabyte=> INSERT INTO tab1 (c1) SELECT 'E';
ERROR: duplicate key value violates unique constraint "tab1_pkey"
yugabyte=> INSERT INTO tab1 (c1) SELECT 'E';
INSERT 0 1
yugabyte=> SELECT * FROM tab1 ORDER BY id, c1;
id | c1
----+----
1 | A
2 | B
3 | C
4 | D
5 | E
(5 rows)
Have Fun!
