0

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;
6
  • 1
    have you tried to use a triggers
    – nbk
    Sep 9, 2021 at 22:12
  • 2
    Since userid is the PK, your desired unique index on (userid, save) does nothing useful (unless you want to point a multicolumn FK constraint to it). Also, updating a row never conflicts with itself, a unique index is the wrong tool. The WHERE clause you have is the right tool. Why would you want to break it? Sep 10, 2021 at 2:39
  • @ErwinBrandstetter The unique index on (userid, save) is because I want to disable updates that happen on the same date (ie. any given row must be updated at most once per day). Let me know if it still doesn't make sense to have the unique index, because your observation is spot on. The only reason I want to do this is because I'm handling this situation in different table (with slightly different logic) with a unique index on (userid, save), and I'd like to handle the 2 tables in the same manner, for consistency. Sep 10, 2021 at 4:36
  • Also, the WHERE solution doesn't throw an error on conflicts (which is not the fault of WHERE, but of ON CONFLICT, I think), and the result is that I need different logic to handle each of the 2 tables, making my code uglier... (I'm using Postgres from Python with psycopg2) Sep 10, 2021 at 4:50
  • 1
    If you don't want to update in case of duplicate rows, then use on conflict do nothing
    – user1822
    Sep 10, 2021 at 5:14

2 Answers 2

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.

5
  • Cheking how many rows were affected... How can I do that? Is that O(n)? Sep 22, 2021 at 8:22
  • That's part of the result state of the INSERT. What API are you using to access PostgreSQL? Sep 22, 2021 at 8:44
  • asyncpg and psycopg2 Sep 22, 2021 at 9:24
  • Ah, wait, this also doesn't really suit me very well because I'm trying to minimize write operations to disk. So, ideally we'd check first whether our intended write is going to modify a row or not. Obviously this can be done by executing another query first that specifically checks for this, but I wonder if the whole thing can be done in a single query (which is definitely more elegant, and maybe even faster) Sep 22, 2021 at 9:28
  • Simply get the rowcount from the cursor, see this answer. Sep 22, 2021 at 9:46
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.