Although it is related to having a root exception type for the adapter, I don't know if pg has one.
The new PG_DIAG_*_NAME error fields IMHO don't help in regards to issue #5. But psycopg does already proper subclassing of exceptions that is described in #5. It's realized through errorcodes.py which is parsed and autogenerated from the postgresql.org documentation. We might adopt some ideas (although I would like parsing from postgresql/src/backend/utils/errorcodes.txt more).
Regarding to the PG_DIAG_*_NAME error fields, no further changes are needed. We already have this link to the result object, which can be used to retrieve the PG_DIAG_* fields.