Psycopg Uniqueviolation: get value that caused the error

Question

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.

Answer 1

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)