UPSERT unless the date is the same

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 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;

Answer 1

Use the INSERT ... ON CONFLICT with the WHERE condition as you showed it.

Then check how many rows were affected, and if there was none, throw an error. You can do that either in client code or in a database function.

With psycopg2, you can get the number of rows modified from the rowcount property of the cursor, as shown in this answer.

Answer 2

So Laurenz had no way of knowing, but, in looking for the most elegant way of doing this, the solution was to modify the INSERT in the OTHER table.

CREATE TABLE IF NOT EXISTS table01(
  userid     int8 NOT NULL,
  save       date NOT NULL,
  followers  int4
);
CREATE  UNIQUE INDEX  ON table01 (userid,save);

INSERT  INTO table01 (userid,save,followers)  VALUES (%s,%s,%s)  ON CONFLICT (userid,save)  DO NOTHING

Now each of the 2 tables can be updated in a single Postgres query and without extra Python boilerplate or logic checks.

Another major advantage (on top of elegance & consistency) is that now I can do all the Postgres operations in a single transactions (because now no transaction raises an Exception), which has speed up writes by ~5x.

So the Python code now looks like:

  async with pool.acquire() as conn, conn.transaction():
    for account in accounts:
      # get data

      # 1) put profile
      await conn.execute('''INSERT  INTO tt_profiles (userid,save,load,username,country,verified,following,followers,likes,videos,engagement,created,nickname,signature)  VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
        ON CONFLICT (userid)  DO  UPDATE SET save=EXCLUDED.save,username=EXCLUDED.username,verified=EXCLUDED.verified,following=EXCLUDED.following,followers=EXCLUDED.followers,likes=EXCLUDED.likes,videos=EXCLUDED.videos,engagement=EXCLUDED.engagement,created=EXCLUDED.created,nickname=EXCLUDED.nickname,signature=EXCLUDED.signature
        WHERE tt_profiles.save!=EXCLUDED.save''', userid,save,load,username,country,verified,following,followers,likes,videos,engagement,created,nickname,signature)

      # 2) put series
      await conn.execute('INSERT  INTO tt_series (userid,save,following,followers,likes,videos,engagement)  VALUES ($1,$2,$3,$4,$5,$6,$7)  ON CONFLICT (userid,save)  DO NOTHING', userid,save,following,followers,likes,videos,engagement)

which is really clean and much faster.