# Trying to use: INSERT ON CONFLICT UPDATE SET **URL:** \ **Category:** SQL\ **Created:** [May 29, 2023, 5:02pm UTC](https://community.cratedb.com/t/trying-to-use-insert-on-conflict-update-set/1497 "2023-05-29T17:02:20Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Andy\_Teasdale](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/andy_teasdale/32/927_2.png) [@Andy\_Teasdale](https://community.cratedb.com/u/Andy_Teasdale)\ **Post date:** [May 29, 2023, 5:02pm UTC](https://community.cratedb.com/t/trying-to-use-insert-on-conflict-update-set/1497/1 "2023-05-29T17:02:20Z")
Hi, Using a single node CrateDB (v5.3.1) install, am getting unexpected behaviour when attempting to use “ON CONFLICT” when inserting. Has anyone else experience with this? ```auto / *************** * Testing INSERT ON CONFLICT UPDATE SET *************** / drop table if exists history_tsx ; CREATE TABLE history_tsx ( name TEXT NOT NULL, pointType TEXT NOT NULL, timestamp TIMESTAMP WITH TIME ZONE NOT NULL, ts_day TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS date_trunc('day', timestamp), stringValue VARCHAR(255), value DOUBLE precision, primary key ("name", "ts_day", "timestamp") ) PARTITIONED BY (ts_day) WITH (number_of_routing_shards = 4, number_of_replicas = 0, "write.wait_for_active_shards" = 1) ; REFRESH TABLE history_tsx ; INSERT INTO history_tsx ("name", "pointtype", "timestamp", "value") VALUES ('DEVICES.1 ', 'Application Point', '2023-05-01T16:37:59.7880000Z', 559) ; REFRESH TABLE history_tsx ; INSERT INTO history_tsx ("name", "pointtype", "timestamp", "value") VALUES ('DEVICES.1 ', 'Application Point', '2023-05-01T16:37:59.7880000Z', 999) ON CONFLICT ("name", "ts_day", "timestamp") DO UPDATE SET value = excluded."value" ; REFRESH TABLE history_tsx ; INSERT INTO history_tsx ("name", "pointtype", "timestamp", "value") VALUES ('DEVICES.2 ', 'Application Point', '2023-05-02T16:37:59.7880000Z', 559), ('DEVICES.2 ', 'Application Point', '2023-05-02T16:37:59.7880000Z', 999) ; REFRESH TABLE history_tsx ; INSERT INTO history_tsx ("name", "pointtype", "timestamp", "value") VALUES ('DEVICES.3 ', 'Application Point', '2023-05-03T16:37:59.7880000Z', 559), ('DEVICES.3 ', 'Application Point', '2023-05-03T16:37:59.7880000Z', 999) ON CONFLICT (name, ts_day, timestamp) DO UPDATE SET value = excluded."value" ; REFRESH TABLE history_tsx ; select "name", "ts_day", "timestamp", value FROM history_tsx ; ------------------------------------------------------------------- -- What I saw: name |ts_day |timestamp | value| -----------+-----------------------+-----------------------+------+ DEVICES.1 |2023-05-01 01:00:00.000|2023-05-01 17:37:59.788| 559.0| DEVICES.2 |2023-05-02 01:00:00.000|2023-05-02 17:37:59.788| 559.0| ------------------------------------------------------------------- -- What I expected to see: name |ts_day |timestamp | value| -----------+-----------------------+-----------------------+------+ DEVICES.1 |2023-05-01 01:00:00.000|2023-05-01 17:37:59.788| 999.0| DEVICES.2 |2023-05-02 01:00:00.000|2023-05-02 17:37:59.788| 559.0| DEVICES.3 |2023-05-03 01:00:00.000|2023-05-02 17:37:59.788| 999.0| ------------------------------------------------------------------- ``` ---
**Author:** ![karynsaz](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/karynsaz/32/611_2.png) [@karynsaz](https://community.cratedb.com/u/karynsaz)\ **Post date:** [May 29, 2023, 6:34pm UTC](https://community.cratedb.com/t/trying-to-use-insert-on-conflict-update-set/1497/2 "2023-05-29T18:34:46Z")
Hi Andy, I tested other versions and could identify that the unexpected behavior began in 5.3.0. In the different versions tested, i.e. 5.2.2, 5.2.6, and 5.2.8 the expected behavior was observed. I’ll report this to the team and keep you posted. In the meantime, I’d suggest you downgrade to a previous version that doesn’t present the reported issue. Thank you! Karyn Azevedo ---
**Author:** ![Andy\_Teasdale](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/andy_teasdale/32/927_2.png) [@Andy\_Teasdale](https://community.cratedb.com/u/Andy_Teasdale)\ **Post date:** [May 30, 2023, 5:39am UTC](https://community.cratedb.com/t/trying-to-use-insert-on-conflict-update-set/1497/3 "2023-05-30T05:39:25Z")
> [@karynsaz](#): > > Karyn Thanks Karyn, appreciate the swift response! Andy ---
**Author:** ![hernanc](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hernanc/32/676_2.png) [@hernanc](https://community.cratedb.com/u/hernanc)\ **Post date:** [May 31, 2023, 7:51am UTC](https://community.cratedb.com/t/trying-to-use-insert-on-conflict-update-set/1497/4 "2023-05-31T07:51:20Z")
Hi, I tested and this problem is now solved in our latest nightly build, so you could use version 5.2.8 for the time being and upgrade to 5.3.3 or 5.4.0 once they are released. Thank you. ---
**Author:** ![Andy\_Teasdale](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/andy_teasdale/32/927_2.png) [@Andy\_Teasdale](https://community.cratedb.com/u/Andy_Teasdale)\ **Post date:** [June 13, 2023, 1:19pm UTC](https://community.cratedb.com/t/trying-to-use-insert-on-conflict-update-set/1497/5 "2023-06-13T13:19:45Z")
> [@hernanc](#): > > 5.3.3 Any date yet when 5.3.3. will be GA? ---
**Author:** ![hernanc](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hernanc/32/676_2.png) [@hernanc](https://community.cratedb.com/u/hernanc)\ **Post date:** [June 27, 2023, 8:39am UTC](https://community.cratedb.com/t/trying-to-use-insert-on-conflict-update-set/1497/6 "2023-06-27T08:39:19Z")
Hi, 5.3.3 was released to the testing channel yesterday and if there are no issues the plan is to promote it to stable before the end of the week.