Return to Question

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 statement. 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)  DO NOTHING;

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;

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 statement. 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."

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 statement. 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)  DO NOTHING;