GENERATED BY DEFAULT AS IDENTITY vs GENERATED ALWAYS AS IDENTITY

Identity columns in YugabyteDB automatically generate unique numbers for a column. They are defined using the GENERATED BY DEFAULT AS IDENTITY or GENERATED ALWAYS AS IDENTITY clause when creating a table column.

An implicit sequence is generated and linked to the identity column, automatically assigning values to new rows.

But what is the difference between using the GENERATED BY DEFAULT AS IDENTITY and the GENERATED ALWAYS AS IDENTITY clauses?

Let’s find out.

We’ll start out with GENERATED BY DEFAULT AS IDENTITY:

				
					yugabyte=# CREATE TABLE some_table_default_ident(id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, some_d
ata TEXT);
CREATE TABLE

yugabyte=# INSERT INTO some_table_default_ident(some_data) VALUES ('A'), ('B'), ('C');
INSERT 0 3

yugabyte=# SELECT * FROM some_table_default_ident ORDER BY id;
 id | some_data
----+-----------
  1 | A
  2 | B
  3 | C
(3 rows)

Next, here’s how the GENERATED ALWAYS AS IDENTITY clause functions:

				
					yugabyte=# CREATE TABLE some_table_always_ident(id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, some_data T
EXT);
CREATE TABLE

yugabyte=# INSERT INTO some_table_always_ident(some_data) VALUES ('A'), ('B'), ('C');
INSERT 0 3

yugabyte=# SELECT * FROM some_table_always_ident ORDER BY id;
 id | some_data
----+-----------
  1 | A
  2 | B
  3 | C
(3 rows)

Hmm, they seem to perform the same way.

However, the differences between the two become apparent when inserting or updating a value in the identity column.

				
					yugabyte=# INSERT INTO some_table_default_ident (id, some_data) VALUES (4, 'D');
INSERT 0 1

yugabyte=# INSERT INTO some_table_always_ident (id, some_data) VALUES (4, 'D');
ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

We successfully inserted a value into the identity column defined with the GENERATED BY DEFAULT AS IDENTITY clause. However, note the behavior differs for an identity column defined with the GENERATED ALWAYS AS IDENTITY clause.

For the some_table_always_ident table, the insert statement requires a special directive:

				
					yugabyte=# INSERT INTO some_table_always_ident (id, some_data) OVERRIDING SYSTEM VALUE VALUES (4, 'D');
INSERT 0 1

Upon checking both tables, we see that each now contains a row with id = 4:

				
					yugabyte=# SELECT * FROM some_table_default_ident ORDER BY id;
 id | some_data
----+-----------
  1 | A
  2 | B
  3 | C
  4 | D
(4 rows)

yugabyte=# SELECT * FROM some_table_always_ident ORDER BY id;
 id | some_data
----+-----------
  1 | A
  2 | B
  3 | C
  4 | D
(4 rows)

When updating an identity column with a different value, the behavior also varies depending on how the identity was defined.

				
					yugabyte=# UPDATE some_table_default_ident SET id = 5 WHERE id = 4;
UPDATE 1

yugabyte=# UPDATE some_table_always_ident SET id = 5 WHERE id = 4;
ERROR:  column "id" can only be updated to DEFAULT
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.

We were able to update the identity column defined with the GENERATED BY DEFAULT AS IDENTITY clause, but not the one defined with the GENERATED ALWAYS AS IDENTITY clause.

The OVERRIDING clause cannot be used for the UPDATE statement.

Here are the final datasets from our experiment:

				
					yugabyte=# SELECT * FROM some_table_default_ident ORDER BY id;
 id | some_data
----+-----------
  1 | A
  2 | B
  3 | C
  5 | D
(4 rows)

yugabyte=# SELECT * FROM some_table_always_ident ORDER BY id;
 id | some_data
----+-----------
  1 | A
  2 | B
  3 | C
  4 | D
(4 rows)

One thing to remember when manually modifying the values of the identity column in the some_table_default_ident table is that the identity column has an underlying sequence. Since we manually inserted and updated the values, the sequence is now out of sync. It’s important to keep this in mind, as the sequence may no longer align with the current values in the table.

				
					yugabyte=# SELECT sequencename FROM pg_sequences;
          sequencename
---------------------------------
 some_table_default_ident_id_seq
 some_table_always_ident_id_seq
(2 rows)

yugabyte=# SELECT currval('some_table_default_ident_id_seq');
 currval
---------
       3
(1 row)

We can fix the underlying sequence with the SETVAL function:

				
					yugabyte=# SELECT setval('some_table_default_ident_id_seq', 6, false);
 setval
--------
      6
(1 row)

yugabyte=# INSERT INTO some_table_default_ident (some_data) VALUES ('E');
INSERT 0 1

yugabyte=# SELECT * FROM some_table_default_ident ORDER BY id;
 id | some_data
----+-----------
  1 | A
  2 | B
  3 | C
  5 | D
  6 | E
(5 rows)

Have Fun!