0

I want to increment search counter when user searches a word.

create table public."SearchLog" (
  id integer primary key not null default nextval('"SearchLog_id_seq"'::regclass),
  search character varying(192),
  replacement character varying(192),
  qty integer,
  visible boolean,
  created_at timestamp(3) without time zone default CURRENT_TIMESTAMP(3),
  updated_at timestamp(3) without time zone default CURRENT_TIMESTAMP(3)
);

when i run the code

insert into "SearchLog" (search)
values ('test')
on conflict(search) do update set qty        = excluded.qty + 1,
                                  updated_at = current_timestamp(3);

i get this error:

[23505] ERROR: duplicate key value violates unique constraint "SearchLog_pkey"

why "on coflict" doesn't trigger? how it can violates pkey if i even didn't provided it?

NeverBe
  • 5,213
  • 2
  • 25
  • 39
  • 1
    Most probably your sequence got out of sync caused by other statements. This is one of the reasons why `identity` columns are preferred over using a sequence and a default value. See [here](https://stackoverflow.com/questions/244243/) or [here](https://stackoverflow.com/questions/9314382) for answers on how to fix the sequence problem. And [here](https://stackoverflow.com/a/59233169/) on how to convert your column to an `identity` column –  Dec 26 '22 at 13:04
  • @a_horse_with_no_name. thanks, i'm new to pg and forgot about seq again. I got this table synced using symmetricsds and have seqs not updated. So it helped me. – NeverBe Dec 26 '22 at 13:11
  • @a_horse_with_no_name. To be clear an `IDENTITY` column does not eliminate this issue. There are a variety of ways a user can enter an out of sequence value in and `IDENNTITY` column and run into the same issue as the OP. What `IDENTITY` provides is a SQL standard way of creating and manipulating an autoincrement field. – Adrian Klaver Dec 26 '22 at 16:26
  • @AdrianKlaver: when using `generated always as identity` you need to explicitly override the system generated values so this is a conscious action. With a serial or `generated by default` bypassing the sequence can happen without noticing it. More often than not the reason for a sequence to get out of sync is caused by _accidentally_ providing a value. –  Dec 26 '22 at 17:02
  • There is no unique constraint on “search” either. Something is missing in this topic – Frank Heikens Dec 26 '22 at 19:01
  • @a_horse_with_no_name, the point is `IDENTITY` has two options and neither is the default. This means the user has to choose and given demonstrated user lack of interest in the docs this means it is likely the permissive choice will be selected and they will right back in the same situation. – Adrian Klaver Dec 26 '22 at 20:32
  • @FrankHeikens, the unique constraint is `SearchLog_pkey` which is the PK. Per [Create Table](https://www.postgresql.org/docs/current/sql-createtable.html): *The PRIMARY KEY constraint specifies that a column or columns of a table can contain only unique (non-duplicate), nonnull values.* – Adrian Klaver Dec 26 '22 at 20:34
  • @AdrianKlaver: and the conflict should be on a different constraint, on search. But that constraint doesn’t exist in the ddl above – Frank Heikens Dec 26 '22 at 21:30
  • Aaah, I missed that. – Adrian Klaver Dec 26 '22 at 21:32

0 Answers0