1

How to get the exact value that caused the Uniqueviolation exception?

The illustrative code is:

try:
    cur.execute("INSERT INTO test (num) VALUES (1), (2), (3), (3);")
    conn.commit()
except psycopg.errors.UniqueViolation as err:
    print("Failed to insert non-unique number: {}".format(???))
    conn.rollback()

so the question is how to print the number "3"? Does the psycopg.errors.UniqueViolation contain the value that violated the constraint at all?

In the real code the values are dynamic. I log the rolled back transaction, but I'd like to also identify the offending number in the list, and ideally repeat the transaction without the duplicate.

Alex Blex
  • 34,704
  • 7
  • 48
  • 75
  • Is there a reason you can't test the input for dupes before running the DB command? – MattDMo Jul 31 '22 at 21:44
  • @MattDMo ok, I probably oversimplified the example just to trigger the exception. It's a database we are talking about. Database has state. Even if I have no duplicates in the parameters, I get the exception because the number is already in the db. The only way to test for duplicates is to run a select query but I'd like to avoid it - I would need to run selects on 100% of inserts, when I have only 0.001% of duplicates. – Alex Blex Jul 31 '22 at 22:10
  • @Alex: Do you really insert multiple values at once or is it just for the simplified example ? – Maurice Meyer Aug 01 '22 at 23:24
  • @MauriceMeyer Yes, I do inserts in batches. Still profiling the optimal size, something between 500 and 1000 lines per batch. Will likely reduce to a 100 if the `err` does not contain any information about the exact duplicate. It will slow down import but increase number of successful transactions. – Alex Blex Aug 02 '22 at 08:33

1 Answers1

0

Use just the exception message:

try:
    cur.execute("INSERT INTO test (num) VALUES (1), (2), (3), (3);")
    conn.commit()
except psycopg.errors.UniqueViolation as err:
    print("Failed to insert non-unique number: {}".format(err))
    conn.rollback()

You should get something like this:

Failed to insert non-unique number: duplicate key value violates unique constraint "test_num_key"
DETAIL:  Key (num)=(3) already exists.

The format of message_detail for unique violation is constant, you can extract the value from the second pair of parenthesis with a regex, e.g.

import re
# ...
    value = re.search(r'\(num\)=\((.*)\)', err.diag.message_detail).group(1)
klin
  • 112,967
  • 15
  • 204
  • 232