-- INSERT .. ON CONFLICT

CREATE SCHEMA ioc;

SET SESSION search_path = 'ioc';

CREATE EXTENSION orioledb;

SELECT orioledb_parallel_debug_start();

orioledb_parallel_debug_start

-------------------------------

(1 row)

---

-- easy cases

---

CREATE TABLE o_test_ioc1

(

id1 int8 NOT NULL,

id2 int8 NOT NULL,

PRIMARY KEY (id1)

) USING orioledb;

INSERT INTO o_test_ioc1 VALUES (1, 1) ON CONFLICT (id1) DO NOTHING;

INSERT INTO o_test_ioc1 VALUES (1, 2) ON CONFLICT (id1) DO NOTHING;

INSERT INTO o_test_ioc1 VALUES (1, 3) ON CONFLICT (not_exist) DO NOTHING;

ERROR: column "not_exist" does not exist

LINE 1: INSERT INTO o_test_ioc1 VALUES (1, 3) ON CONFLICT (not_exist...

^

INSERT INTO o_test_ioc1 VALUES (1, 4) ON CONFLICT DO NOTHING;

INSERT INTO o_test_ioc1 VALUES (1, 5) ON CONFLICT DO NOTHING RETURNING *;

id1 | id2

-----+-----

(0 rows)

SELECT * FROM o_test_ioc1;

id1 | id2

-----+-----

1 | 1

(1 row)

TRUNCATE o_test_ioc1;

INSERT INTO o_test_ioc1 AS t

VALUES (1, 1), (1, 2)

ON CONFLICT (id1) DO UPDATE SET id2 = t.id2 + EXCLUDED.id2;

ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time

HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

CREATE UNIQUE INDEX o_test_ioc1_uniq on o_test_ioc1(id2);

ALTER TABLE o_test_ioc1 ADD COLUMN val int;

INSERT INTO o_test_ioc1 AS t

VALUES (1, 1, 1), (2, 1, 2)

ON CONFLICT (id2) DO UPDATE SET val = t.val + EXCLUDED.val;

ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time

HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

CREATE TABLE o_test_ioc2

(

id1 int8 NOT NULL,

id2 int8 NOT NULL,

PRIMARY KEY (id1)

) USING orioledb;

INSERT INTO o_test_ioc2 VALUES (1, 1) ON CONFLICT (id1) DO NOTHING;

INSERT INTO o_test_ioc2 VALUES (1, 2) ON CONFLICT (id1) DO NOTHING;

INSERT INTO o_test_ioc2 VALUES (1, 3) ON CONFLICT (not_exist) DO NOTHING;

ERROR: column "not_exist" does not exist

LINE 1: INSERT INTO o_test_ioc2 VALUES (1, 3) ON CONFLICT (not_exist...

^

INSERT INTO o_test_ioc2 VALUES (1, 4) ON CONFLICT DO NOTHING;

INSERT INTO o_test_ioc2 VALUES (1, 5) ON CONFLICT DO NOTHING RETURNING *;

id1 | id2

-----+-----

(0 rows)

SELECT * FROM o_test_ioc2;

id1 | id2

-----+-----

1 | 1

(1 row)

CREATE TABLE o_test_ioc3

(

id1 int8 NOT NULL,

id2 int8 NOT NULL

) USING orioledb;

CREATE INDEX o_test_ioc3_reg ON o_test_ioc3 (id1);

INSERT INTO o_test_ioc3 VALUES (1, 1) ON CONFLICT (id1) DO NOTHING;

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

INSERT INTO o_test_ioc3 VALUES (1, 2) ON CONFLICT (id1) DO NOTHING;

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

INSERT INTO o_test_ioc3 VALUES (1, 3) ON CONFLICT (not_exist) DO NOTHING;

ERROR: column "not_exist" does not exist

LINE 1: INSERT INTO o_test_ioc3 VALUES (1, 3) ON CONFLICT (not_exist...

^

INSERT INTO o_test_ioc3 VALUES (1, 4) ON CONFLICT DO NOTHING;

INSERT INTO o_test_ioc3 VALUES (1, 5) ON CONFLICT DO NOTHING RETURNING *;

id1 | id2

-----+-----

1 | 5

(1 row)

SELECT * FROM o_test_ioc3;

id1 | id2

-----+-----

1 | 4

1 | 5

(2 rows)

---

-- conflict_target tests

---

CREATE TABLE o_test_ioc4

(

id1 int8 NOT NULL,

id2 int8 NOT NULL,

id3 int8 NOT NULL,

id4 int8 NOT NULL,

id5 int8 NOT NULL,

id6 int8 NOT NULL,

id7 int8 NOT NULL,

id8 int8 NOT NULL,

PRIMARY KEY(id4)

) USING orioledb;

CREATE UNIQUE INDEX o_test_ioc4_uniq1 on o_test_ioc4(id5);

CREATE UNIQUE INDEX o_test_ioc4_uniq2 on o_test_ioc4(id4, id5);

CREATE UNIQUE INDEX o_test_ioc4_uniq3 on o_test_ioc4(id6, id7);

CREATE INDEX o_test_ioc4_reg1 on o_test_ioc4(id3);

CREATE UNIQUE INDEX o_test_ioc4_uniq4 on o_test_ioc4(id2);

INSERT INTO o_test_ioc4 VALUES (1, 2, 3, 4, 5, 6, 7, 8);

INSERT INTO o_test_ioc4 VALUES (1, 2, 3, 4, 5, 6, 7, 8) ON CONFLICT (id1) DO NOTHING;

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

INSERT INTO o_test_ioc4 VALUES (9, 2, 9, 9, 9, 9, 9, 9) ON CONFLICT (id2) DO NOTHING;

INSERT INTO o_test_ioc4 VALUES (1, 2, 3, 4, 5, 6, 7, 8) ON CONFLICT (id3) DO NOTHING;

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

INSERT INTO o_test_ioc4 VALUES (9, 9, 9, 4, 9, 9, 9, 9) ON CONFLICT (id4) DO NOTHING;

INSERT INTO o_test_ioc4 VALUES (9, 9, 9, 9, 5, 9, 9, 9) ON CONFLICT (id5) DO NOTHING;

INSERT INTO o_test_ioc4 VALUES (1, 2, 3, 4, 5, 6, 7, 8) ON CONFLICT (id2, id3) DO NOTHING;

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

INSERT INTO o_test_ioc4 VALUES (1, 2, 3, 4, 5, 6, 7, 8) ON CONFLICT (id3, id4) DO NOTHING;

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

INSERT INTO o_test_ioc4 VALUES (9, 9, 9, 4, 5, 9, 9, 9) ON CONFLICT (id4, id5) DO NOTHING;

INSERT INTO o_test_ioc4 VALUES (1, 2, 3, 4, 5, 6, 7, 8) ON CONFLICT (id5, id6) DO NOTHING;

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

INSERT INTO o_test_ioc4 VALUES (9, 9, 9, 9, 9, 6, 7, 9) ON CONFLICT (id6, id7) DO NOTHING;

INSERT INTO o_test_ioc4 VALUES (1, 2, 3, 4, 5, 6, 7, 8) ON CONFLICT (id3, id4, id5) DO NOTHING;

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

SELECT * FROM o_test_ioc4;

id1 | id2 | id3 | id4 | id5 | id6 | id7 | id8

-----+-----+-----+-----+-----+-----+-----+-----

1 | 2 | 3 | 4 | 5 | 6 | 7 | 8

(1 row)

CREATE TABLE o_test_ioc5

(

id1 int8 NOT NULL,

id2 int8 NOT NULL,

id3 int8 NOT NULL,

id4 int8 NOT NULL,

id5 int8 NOT NULL,

id6 int8 NOT NULL,

id7 int8 NOT NULL,

id8 int8 NOT NULL,

PRIMARY KEY(id1)

) USING orioledb;

CREATE UNIQUE INDEX o_test_ioc5_uniq1 on o_test_ioc5(id4);

CREATE UNIQUE INDEX o_test_ioc5_uniq2 on o_test_ioc5(id4);

CREATE UNIQUE INDEX o_test_ioc5_uniq3 on o_test_ioc5(id5);

CREATE UNIQUE INDEX o_test_ioc5_uniq4 on o_test_ioc5(id4, id5);

CREATE UNIQUE INDEX o_test_ioc5_uniq5 on o_test_ioc5(id6, id7);

CREATE INDEX o_test_ioc5_reg1 on o_test_ioc5(id3);

CREATE UNIQUE INDEX o_test_ioc5_uniq6 on o_test_ioc5(id2);

INSERT INTO o_test_ioc5 VALUES (1, 2, 3, 4, 5, 6, 7, 8);

INSERT INTO o_test_ioc5 VALUES (1, 9, 9, 9, 9, 9, 9, 9) ON CONFLICT (id1) DO NOTHING;

INSERT INTO o_test_ioc5 VALUES (9, 2, 9, 9, 9, 9, 9, 9) ON CONFLICT (id2) DO NOTHING;

INSERT INTO o_test_ioc5 VALUES (1, 2, 3, 4, 5, 6, 7, 8) ON CONFLICT (id3) DO NOTHING;

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

INSERT INTO o_test_ioc5 VALUES (9, 9, 9, 4, 9, 9, 9, 9) ON CONFLICT (id4) DO NOTHING;

INSERT INTO o_test_ioc5 VALUES (9, 9, 9, 9, 5, 9, 9, 9) ON CONFLICT (id5) DO NOTHING;

INSERT INTO o_test_ioc5 VALUES (1, 2, 3, 4, 5, 6, 7, 8) ON CONFLICT (id2, id3) DO NOTHING;

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

INSERT INTO o_test_ioc5 VALUES (1, 2, 3, 4, 5, 6, 7, 8) ON CONFLICT (id3, id4) DO NOTHING;

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

INSERT INTO o_test_ioc5 VALUES (9, 9, 9, 4, 5, 9, 9, 9) ON CONFLICT (id4, id5) DO NOTHING;

INSERT INTO o_test_ioc5 VALUES (9, 9, 9, 4, 9, 9, 9, 9) ON CONFLICT (id4, id5) DO NOTHING;

ERROR: duplicate key value violates unique constraint "o_test_ioc5_uniq1"

DETAIL: Key (id4)=('4') already exists.

INSERT INTO o_test_ioc5 VALUES (9, 9, 9, 9, 5, 9, 9, 9) ON CONFLICT (id4, id5) DO NOTHING;

ERROR: duplicate key value violates unique constraint "o_test_ioc5_uniq3"

DETAIL: Key (id5)=('5') already exists.

INSERT INTO o_test_ioc5 VALUES (1, 2, 3, 4, 5, 6, 7, 8) ON CONFLICT (id5, id6) DO NOTHING;

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

INSERT INTO o_test_ioc5 VALUES (9, 9, 9, 9, 9, 6, 7, 9) ON CONFLICT (id6, id7) DO NOTHING;

INSERT INTO o_test_ioc5 VALUES (1, 2, 3, 4, 5, 6, 7, 8) ON CONFLICT (id3, id4, id5) DO NOTHING;

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

SELECT * FROM o_test_ioc5;

id1 | id2 | id3 | id4 | id5 | id6 | id7 | id8

-----+-----+-----+-----+-----+-----+-----+-----

1 | 2 | 3 | 4 | 5 | 6 | 7 | 8

(1 row)

---

-- UPDATE SET

---

CREATE TABLE o_test_ioc6

(

id1 int8 NOT NULL,

id2 int8 NOT NULL,

id3 int8 NOT NULL,

id4 int8 NOT NULL,

id5 int8 NOT NULL,

id6 int8 NOT NULL,

id7 int8 NOT NULL,

id8 int8 NOT NULL,

PRIMARY KEY(id1)

) USING orioledb;

CREATE UNIQUE INDEX o_test_ioc6_uniq1 on o_test_ioc6(id4);

CREATE UNIQUE INDEX o_test_ioc6_uniq2 on o_test_ioc6(id4);

CREATE UNIQUE INDEX o_test_ioc6_uniq3 on o_test_ioc6(id5);

CREATE UNIQUE INDEX o_test_ioc6_uniq4 on o_test_ioc6(id4, id5);

CREATE UNIQUE INDEX o_test_ioc6_uniq5 on o_test_ioc6(id6, id7);

CREATE INDEX o_test_ioc6_reg1 on o_test_ioc6(id3);

CREATE UNIQUE INDEX o_test_ioc6_uniq6 on o_test_ioc6(id2);

INSERT INTO o_test_ioc6 VALUES (1, 2, 3, 4, 5, 6, 7, 8);

INSERT INTO o_test_ioc6 VALUES (1, 9, 9, 9, 9, 9, 9, 9)

ON CONFLICT (id1) DO UPDATE SET

id1 = 10

RETURNING *;

id1 | id2 | id3 | id4 | id5 | id6 | id7 | id8

-----+-----+-----+-----+-----+-----+-----+-----

10 | 2 | 3 | 4 | 5 | 6 | 7 | 8

(1 row)

INSERT INTO o_test_ioc6 VALUES (9, 2, 9, 9, 9, 9, 9, 9)

ON CONFLICT (id2) DO UPDATE SET

id2 = 10

RETURNING *;

id1 | id2 | id3 | id4 | id5 | id6 | id7 | id8

-----+-----+-----+-----+-----+-----+-----+-----

10 | 10 | 3 | 4 | 5 | 6 | 7 | 8

(1 row)

INSERT INTO o_test_ioc6 VALUES (9, 9, 9, 4, 9, 9, 9, 9)

ON CONFLICT (id4) DO UPDATE SET

id4 = 10

RETURNING *;

id1 | id2 | id3 | id4 | id5 | id6 | id7 | id8

-----+-----+-----+-----+-----+-----+-----+-----

10 | 10 | 3 | 10 | 5 | 6 | 7 | 8

(1 row)

INSERT INTO o_test_ioc6 VALUES (9, 9, 9, 9, 5, 9, 9, 9)

ON CONFLICT (id5) DO UPDATE SET

id5 = 10

RETURNING *;

id1 | id2 | id3 | id4 | id5 | id6 | id7 | id8

-----+-----+-----+-----+-----+-----+-----+-----

10 | 10 | 3 | 10 | 10 | 6 | 7 | 8

(1 row)

INSERT INTO o_test_ioc6 VALUES (9, 9, 9, 10, 10, 9, 9, 9)

ON CONFLICT (id4, id5) DO UPDATE SET

id4 = 11, id5 = 11

RETURNING *;

id1 | id2 | id3 | id4 | id5 | id6 | id7 | id8

-----+-----+-----+-----+-----+-----+-----+-----

10 | 10 | 3 | 11 | 11 | 6 | 7 | 8

(1 row)

INSERT INTO o_test_ioc6 VALUES (9, 9, 9, 9, 9, 6, 7, 9)

ON CONFLICT (id6, id7) DO UPDATE SET

id6 = 10, id7 = 10

RETURNING *;

id1 | id2 | id3 | id4 | id5 | id6 | id7 | id8

-----+-----+-----+-----+-----+-----+-----+-----

10 | 10 | 3 | 11 | 11 | 10 | 10 | 8

(1 row)

SELECT * FROM o_test_ioc6;

id1 | id2 | id3 | id4 | id5 | id6 | id7 | id8

-----+-----+-----+-----+-----+-----+-----+-----

10 | 10 | 3 | 11 | 11 | 10 | 10 | 8

(1 row)

INSERT INTO o_test_ioc6 VALUES (10, 10, 10, 10, 10, 10, 10, 10)

ON CONFLICT (id1) DO UPDATE SET

id1 = EXCLUDED.id1 - 100,

id2 = EXCLUDED.id2 - 100,

id3 = EXCLUDED.id3 - 100,

id4 = EXCLUDED.id4 - 100,

id5 = o_test_ioc6.id5 + 100,

id6 = o_test_ioc6.id6 + 100,

id7 = o_test_ioc6.id7 + 100,

id8 = o_test_ioc6.id8 + 100

RETURNING *;

id1 | id2 | id3 | id4 | id5 | id6 | id7 | id8

-----+-----+-----+-----+-----+-----+-----+-----

-90 | -90 | -90 | -90 | 111 | 110 | 110 | 108

(1 row)

SELECT * FROM o_test_ioc6;

id1 | id2 | id3 | id4 | id5 | id6 | id7 | id8

-----+-----+-----+-----+-----+-----+-----+-----

-90 | -90 | -90 | -90 | 111 | 110 | 110 | 108

(1 row)

---

-- UPDATE SET WHERE

---

INSERT INTO o_test_ioc6 VALUES (-90, 1, 1, 1, 1, 1, 1, 1)

ON CONFLICT (id1) DO UPDATE SET

id1 = 1,

id5 = 112

WHERE o_test_ioc6.id5 <> 111

RETURNING *;

id1 | id2 | id3 | id4 | id5 | id6 | id7 | id8

-----+-----+-----+-----+-----+-----+-----+-----

(0 rows)

SELECT * FROM o_test_ioc6;

id1 | id2 | id3 | id4 | id5 | id6 | id7 | id8

-----+-----+-----+-----+-----+-----+-----+-----

-90 | -90 | -90 | -90 | 111 | 110 | 110 | 108

(1 row)

INSERT INTO o_test_ioc6 VALUES (-90, 1, 1, 1, 1, 1, 1, 1)

ON CONFLICT (id1) DO UPDATE SET

id1 = 1,

id5 = 112

WHERE o_test_ioc6.id5 = 111

RETURNING *;

id1 | id2 | id3 | id4 | id5 | id6 | id7 | id8

-----+-----+-----+-----+-----+-----+-----+-----

1 | -90 | -90 | -90 | 112 | 110 | 110 | 108

(1 row)

SELECT * FROM o_test_ioc6;

id1 | id2 | id3 | id4 | id5 | id6 | id7 | id8

-----+-----+-----+-----+-----+-----+-----+-----

1 | -90 | -90 | -90 | 112 | 110 | 110 | 108

(1 row)

CREATE TABLE o_test_ioc_change_pkey (

val_1 int,

val_2 int,

PRIMARY KEY(val_1)

) USING orioledb;

INSERT INTO o_test_ioc_change_pkey VALUES (1, 2), (2, 3);

TABLE o_test_ioc_change_pkey;

val_1 | val_2

-------+-------

1 | 2

2 | 3

(2 rows)

BEGIN;

SELECT orioledb_tbl_structure('o_test_ioc_change_pkey'::regclass, 'ne');

orioledb_tbl_structure

---------------------------------------------------------------------

Index o_test_ioc_change_pkey_pkey contents +

Page 0: level = 0, maxKeyLen = 8, nVacatedBytes = 0 +

state = free, datoid equal, relnode equal, ix_type = primary, dirty+

Leftmost, Rightmost +

Chunk 0: offset = 0, location = 256, hikey location = 64 +

Item 0: offset = 264, tuple = ('1', '2') +

Item 1: offset = 288, tuple = ('2', '3') +

+

Index toast: not loaded +

(1 row)

UPDATE o_test_ioc_change_pkey SET val_1 = 13 WHERE val_1 = 1;

TABLE o_test_ioc_change_pkey;

val_1 | val_2

-------+-------

2 | 3

13 | 2

(2 rows)

SELECT orioledb_tbl_structure('o_test_ioc_change_pkey'::regclass, 'ne');

orioledb_tbl_structure

-------------------------------------------------------------------------

Index o_test_ioc_change_pkey_pkey contents +

Page 0: level = 0, maxKeyLen = 8, nVacatedBytes = 24 +

state = free, datoid equal, relnode equal, ix_type = primary, dirty +

Leftmost, Rightmost +

Chunk 0: offset = 0, location = 256, hikey location = 64 +

Item 0: mode = update, PK changed, offset = 264, tuple = ('1', '2')+

Undo item: +

Item 1: offset = 288, tuple = ('2', '3') +

Item 2: mode = update, offset = 312, tuple = ('13', '2') +

+

Index toast: not loaded +

(1 row)

INSERT INTO o_test_ioc_change_pkey VALUES (1, 15) ON CONFLICT (val_1)

DO UPDATE SET val_1 = 3 RETURNING *;

val_1 | val_2

-------+-------

1 | 15

(1 row)

COMMIT;

TABLE o_test_ioc_change_pkey;

val_1 | val_2

-------+-------

1 | 15

2 | 3

13 | 2

(3 rows)

SELECT orioledb_parallel_debug_stop();

orioledb_parallel_debug_stop

------------------------------

(1 row)

DROP EXTENSION orioledb CASCADE;

NOTICE: drop cascades to 7 other objects

DETAIL: drop cascades to table o_test_ioc1

drop cascades to table o_test_ioc2

drop cascades to table o_test_ioc3

drop cascades to table o_test_ioc4

drop cascades to table o_test_ioc5

drop cascades to table o_test_ioc6

drop cascades to table o_test_ioc_change_pkey

DROP SCHEMA ioc CASCADE;

RESET search_path;