There's a table
CREATE TABLE IF NOT EXISTS table00 ( userid int8 PRIMARY KEY, save date NOT NULL, value int4 );
where I want to upsert values IFF the
date is not the same for a given
userid, which I'm doing like so:
INSERT INTO table00 (userid,save,value) VALUES (%s,%s,%s) ON CONFLICT (userid) DO UPDATE SET save=EXCLUDED.save, value=EXCLUDED.value WHERE table00.save!=EXCLUDED.save;
But I'd prefer to add
CREATE UNIQUE INDEX ON table00 (userid, save);
and drop the
WHERE clause. Is that possible?
But this isn't working, since values are still being updated even if they're on the same day; ie. the
ON CONFLICT seems to swallow up all errors and do the upsert anyways regardless of any uniqueness constraints.
The logic I want is: "If the
userid doesn't exist, then insert a new row. If the
userid exists, then update its row, unless the date is the same, in which case fail."
So, something like:
INSERT INTO table00 (userid,save,followers) VALUES (%s,%s,%s) ON CONFLICT (userid) DO UPDATE SET save=EXCLUDED.save, followers=EXCLUDED.followers ON CONFLICT (userid,date) ERROR;