IntegrityError: distinguish between unique constraint and not null violations

Question

I have this code:

try:
    principal = cls.objects.create(
        user_id=user.id,
        email=user.email,
        path='something'
    )
except IntegrityError:
    principal = cls.objects.get(
        user_id=user.id,
        email=user.email
    )

It tries to create a user with the given id and email, and if there already exists one - tries to get the existing record.

I know this is a bad construction and it will be refactored anyway. But my question is this:

How do i determine what kind of IntegrityError has happened: the one related to unique constraint violation (there is unique key on (user_id, email)) or the one related to not null constraint (path cannot be null)?

Answer 1

psycopg2 provides the SQLSTATE with the exception as the pgcode member, which gives you quite fine-grained error information to match on.

python3
>>> import psycopg2
>>> conn = psycopg2.connect("dbname=regress")
>>> curs = conn.cursor()
>>> try:
...     curs.execute("INVALID;")
... except Exception as ex:
...     xx = ex
>>> xx.pgcode
'42601'

See Appendix A: Error Codes in the PostgreSQL manual for code meanings. Note that you can match coarsely on the first two chars for broad categories. In this case I can see that SQLSTATE 42601 is syntax_error in the Syntax Error or Access Rule Violation category.

The codes you want are:

23505   unique_violation
23502   not_null_violation

so you could write:

try:
    principal = cls.objects.create(
        user_id=user.id,
        email=user.email,
        path='something'
    )
except IntegrityError as ex:
    if ex.pgcode == '23505':
        principal = cls.objects.get(
            user_id=user.id,
            email=user.email
        )
    else:
        raise

That said, this is a bad way to do an upsert or merge. @pr0gg3d is presumably right in suggesting the right way to do it with Django; I don't do Django so I can't comment on that bit. For general info on upsert/merge see depesz's article on the topic.

Answer 2

Update as of 9-6-2017:

A pretty elegant way to do this is to try/except IntegrityError as exc, and then use some useful attributes on exc.__cause__ and exc.__cause__.diag (a diagnostic class that gives you some other super relevant information on the error at hand - you can explore it yourself with dir(exc.__cause__.diag)).

The first one you can use was described above. To make your code more future proof you can reference the psycopg2 codes directly, and you can even check the constraint that was violated using the diagnostic class I mentioned above:

except IntegrityError as exc:
    from psycopg2 import errorcodes as pg_errorcodes
    assert exc.__cause__.pgcode == pg_errorcodes.UNIQUE_VIOLATION
    assert exc.__cause__.diag.constraint_name == 'tablename_colA_colB_unique_constraint'

edit for clarification: I have to use the __cause__ accessor because I'm using Django, so to get to the psycopg2 IntegrityError class I have to call exc.__cause__