In YugabyteDB‘s YSQL API, a Primary Key constraint is a means to uniquely identify a specific row in a table via one or more columns.
Typically when you insert a new row into a table with values that already exist in the table’s Primary Key column(s), you’ll get an error.
Example:
yugabyte=# CREATE TABLE test (c1 INT PRIMARY KEY, c2 VARCHAR);
CREATE TABLE
yugabyte=# INSERT INTO test VALUES (1, 'A'), (2, 'B'), (3, 'C');
INSERT 0 3
yugabyte=# SELECT * FROM test;
c1 | c2
----+----
1 | A
2 | B
3 | C
(3 rows)
yugabyte=# INSERT INTO test VALUES (1, 'Z');
ERROR: duplicate key value violates unique constraint "test_pkey"
You can override this behavior by turning on the yb_enable_upsert_mode YSQL parameter.
Doing so will enable upsert mode, causing INSERT statements to update an existing row if a specified value already exists in a table, and insert a new row if the specified value doesn’t already exist.
Example:
yugabyte=# SHOW yb_enable_upsert_mode; -- The default value
yb_enable_upsert_mode
-----------------------
off
(1 row)
yugabyte=# INSERT INTO test VALUES (1, 'Z');
ERROR: duplicate key value violates unique constraint "test_pkey"
yugabyte=# SET yb_enable_upsert_mode=on;
SET
yugabyte=# INSERT INTO test VALUES (1, 'Z');
INSERT 0 1
yugabyte=# INSERT INTO test VALUES (4, 'D');
INSERT 0 1
yugabyte=# SELECT * FROM test ORDER BY 1;
c1 | c2
----+----
1 | Z
2 | B
3 | C
4 | D
(4 rows)
Have Fun!