Coming from Mysql world, we have a process to insert user jobs log into a table like this.
CREATE TABLE joblog (
id INT NOT NULL DEFAULT unique_rowid(),
uid INT NOT NULL,
jid INT NOT NULL,
date DATE NOT NULL,
CONSTRAINT “primary” PRIMARY KEY (id ASC),
INDEX joblog_idx (uid ASC),
UNIQUE INDEX uk_joblog (date ASC, jid ASC, uid ASC)
In my old application, I can insert log like this
insert ignore into joblog (UID, jid, date) values
Which two records are the same and without issue.
Howerver, in CRDB
insert into joblog (uID, jid, date) values
on conflict(date,jid,uID) do nothing;
I will get if the table does not contain this unqiue row
pq: duplicate key value (date,jid,uID)=(‘2017-12-17’,5504,1608172896) violates unique constraint “uk_joblog”
If the table contains this unique row, the error will be
pq: UPSERT/ON CONFLICT DO UPDATE command cannot affect row a second time
Not sure if this is a bug, if it is not, how can I do this in CRDB way?