Postgres `ON CONFLICT` updates a variable instead of a TABLE, syntax error at or near ""

Question

I created a postgres query that inserts a series of unique codes. If one code is a duplicate I run the following.

ON CONFLICT ON CONSTRAINT uniquecode DO NOTHING;

What I want to do instead of NOTHING is "counter" = "counter" - 1; because within my while loop "counter" is always incremented by 1. If there is a duplicate then I want it reduced so that if I pass in 10 as the number of generated codes, then it will always return 10 unique codes instead of 9 sometimes because one was a duplicate.

However, I receive the following error when I replaceDO NOTHING or NOTHING with the counter reduction.

syntax error at or near "count"

What is this syntax error. All examples I have seen online show table updates instead. Can I simple update a variable instead?

As per the link posted by @a_horse_with_no_name I tried to apply the below after the conflict.

ON CONFLICT ON CONSTRAINT uniquecode DO UPDATE SET "created_at" = NOW();

    PERFORM * FROM (
      SELECT c."created_at"
      FROM "codes" as c
      ORDER BY "created_at" DESC
      LIMIT 1
    ) AS "newest";
    IF ("newest"::xmax::text::int > 0) THEN
      "counter" = "counter" - 1;
    END IF;

The intention of the above is that I only reduce the counter if the xmax greater than zero, which means that it was updated not inserted. If updated then that means that "created_at" was changed in the conflict, else do nothing.

Currently, I am at a point where if I keep the PERFORM call then either "type" xmax does not exist or "column" xmax does not exist.

You can only update column values of the target table in the `on conflict clause` — , Aug 27 '18 at 14:45
You might want something like this: https://stackoverflow.com/questions/34762732 — , Aug 27 '18 at 14:47
@a_horse_with_no_name I am not sure how to apply this to my issue — Brandon, Aug 27 '18 at 15:57

score 0 · Answer 1 · answered Aug 27 '18 at 14:50

0

You might use:

ON CONFLICT ON CONSTRAINT uniquecode
DO UPDATE SET count = youtable.column - 1

answered Aug 27 '18 at 14:50

Rafael Araújo

201
1
8

There is no table element to update, only a variable. – Brandon Aug 27 '18 at 16:52
In this case, your question has incomplete details. You should say that you are running a function and what type of function. As we don't have further details, I would say that you have to catch the update transaction exception and then change your variable. Definitely, it is not a upsert case. On conflict would just have the INSERT transaction table scope. – Rafael Araújo Aug 27 '18 at 21:10

score 0 · Accepted Answer · answered Aug 28 '18 at 13:22

0

GET DIAGNOSTICS "found" = ROW_COUNT;
IF ("found" = true) THEN "count" = "count" + 1; END IF;

The above solved my problem. It checks if there was change in the previous code block, if not, then it won't update.

answered Aug 28 '18 at 13:22

Brandon

1,447
2
21
41

Postgres `ON CONFLICT` updates a variable instead of a TABLE, syntax error at or near ""

2 Answers2