In YSQL the column constraint GENERATED ALWAYS AS IDENTITY allows you to automatically assign a unique number to a column.
Example:
yugabyte=# CREATE TABLE t1 (id INTEGER GENERATED ALWAYS AS IDENTITY, some_value INT);
CREATE TABLE
yugabyte=# INSERT INTO t1(some_value) VALUES (1), (2), (3);
INSERT 0 3
yugabyte=# SELECT * FROM t1 ORDER BY id;
id | some_value
----+------------
1 | 1
2 | 2
3 | 3
(3 rows)
If you TRUNCATE a table having GENERATED ALWAYS AS IDENITY columns, you’ll find that those columns do not restart again the next time data is inserted.
yugabyte=# TRUNCATE TABLE t1;
TRUNCATE TABLE
yugabyte=# INSERT INTO t1(some_value) VALUES (1), (2), (3);
INSERT 0 3
yugabyte=# SELECT * FROM t1 ORDER BY id;
id | some_value
----+------------
4 | 1
5 | 2
6 | 3
(3 rows)
If you’d prefer that the GENEREATED ALWAYS AS IDENTITY columns restart following a table TRUNCATE, run the TRUNCATE command with the RESTART IDENTITY CASCADE option.
yugabyte=# TRUNCATE TABLE t1 RESTART IDENTITY CASCADE;
TRUNCATE TABLE
yugabyte=# INSERT INTO t1(some_value) VALUES (1), (2), (3);
INSERT 0 3
yugabyte=# SELECT * FROM t1 ORDER BY id;
id | some_value
----+------------
1 | 1
2 | 2
3 | 3
(3 rows)
Have Fun!
