0
# Error Handling and Diagnostics
1

2
Complete PostgreSQL error code mapping to Python exceptions with detailed error information and diagnostic capabilities for robust error handling and debugging.
3

4
## Capabilities
5

6
### Exception Hierarchy
7

8
Complete hierarchy of PostgreSQL-specific exceptions following the DB API 2.0 specification.
9

10
```python { .api }
11
class Error(Exception):
12
    """Base exception class for all psycopg2 errors."""
13

14
class Warning(Exception):
15
    """Exception for database warnings."""
16

17
class InterfaceError(Error):
18
    """Exception for interface-related errors."""
19

20
class DatabaseError(Error):
21
    """Exception for database engine errors."""
22

23
class DataError(DatabaseError):
24
    """Exception for data-related errors (invalid data, numeric overflow, etc.)."""
25

26
class OperationalError(DatabaseError):
27
    """Exception for operation-related errors (disconnect, memory allocation, etc.)."""
28

29
class IntegrityError(DatabaseError):
30
    """Exception for database integrity violations (foreign key, unique constraints, etc.)."""
31

32
class InternalError(DatabaseError):
33
    """Exception for database internal errors (cursor not valid, etc.)."""
34

35
class ProgrammingError(DatabaseError):
36
    """Exception for SQL programming errors (table not found, syntax error, etc.)."""
37

38
class NotSupportedError(DatabaseError):
39
    """Exception for unsupported operations (unsupported function, API, etc.)."""
40
```
41

42
### Specialized Exception Classes
43

44
Additional exception classes for specific PostgreSQL error conditions.
45

46
```python { .api }
47
class QueryCanceledError(OperationalError):
48
    """Exception for query cancellation."""
49

50
class TransactionRollbackError(OperationalError):
51
    """Exception for transaction rollback conditions."""
52
```
53

54
**Usage Example:**
55

56
```python
57
import psycopg2
58
from psycopg2 import (
59
    Error, DatabaseError, IntegrityError, ProgrammingError,
60
    OperationalError, DataError
61
)
62

63
conn = psycopg2.connect("host=localhost dbname=mydb user=myuser password=mypass")
64

65
try:
66
    with conn.cursor() as cur:
67
        # This will raise ProgrammingError if table doesn't exist
68
        cur.execute("SELECT * FROM nonexistent_table")
69
        
70
except ProgrammingError as e:
71
    print(f"SQL Programming Error: {e}")
72
    print(f"Error code: {e.pgcode}")
73
    print(f"Error class: {e.__class__.__name__}")
74

75
try:
76
    with conn.cursor() as cur:
77
        # This will raise IntegrityError if violates unique constraint
78
        cur.execute("INSERT INTO users (email) VALUES (%s)", ("duplicate@email.com",))
79
        conn.commit()
80
        
81
except IntegrityError as e:
82
    print(f"Database Integrity Error: {e}")
83
    print(f"SQLSTATE: {e.pgcode}")
84
    conn.rollback()
85

86
try:
87
    with conn.cursor() as cur:
88
        # This will raise DataError for invalid data type
89
        cur.execute("INSERT INTO users (age) VALUES (%s)", ("not_a_number",))
90
        
91
except DataError as e:
92
    print(f"Data Error: {e}")
93
    conn.rollback()
94

95
conn.close()
96
```
97

98
### Error Code Lookup
99

100
Functions to look up error information by PostgreSQL error codes.
101

102
```python { .api }
103
def lookup(code):
104
    """
105
    Look up exception class by error code.
106
    
107
    Parameters:
108
    - code (str): PostgreSQL SQLSTATE error code
109
    
110
    Returns:
111
    type: Exception class corresponding to the error code
112
    """
113
```
114

115
**Usage Example:**
116

117
```python
118
from psycopg2.errors import lookup
119
from psycopg2.errorcodes import UNIQUE_VIOLATION, FOREIGN_KEY_VIOLATION
120

121
# Look up exception classes
122
unique_error_class = lookup(UNIQUE_VIOLATION)  # '23505'
123
fk_error_class = lookup(FOREIGN_KEY_VIOLATION)  # '23503'
124

125
print(f"Unique violation maps to: {unique_error_class.__name__}")
126
print(f"Foreign key violation maps to: {fk_error_class.__name__}")
127

128
# Use in exception handling
129
try:
130
    # Database operation that might fail
131
    cur.execute("INSERT INTO users (email) VALUES (%s)", ("duplicate@email.com",))
132
except unique_error_class as e:
133
    print(f"Caught unique violation: {e}")
134
except fk_error_class as e:
135
    print(f"Caught foreign key violation: {e}")
136
```
137

138
### Error Code Constants
139

140
Comprehensive constants for PostgreSQL error codes and classes.
141

142
```python { .api }
143
# Error class constants (2-character codes)
144
CLASS_SUCCESSFUL_COMPLETION: str  # '00'
145
CLASS_WARNING: str                # '01'
146
CLASS_NO_DATA: str               # '02'
147
CLASS_SQL_STATEMENT_NOT_YET_COMPLETE: str  # '03'
148
CLASS_CONNECTION_EXCEPTION: str   # '08'
149
CLASS_TRIGGERED_ACTION_EXCEPTION: str  # '09'
150
CLASS_FEATURE_NOT_SUPPORTED: str # '0A'
151
CLASS_INVALID_TRANSACTION_INITIATION: str  # '0B'
152
CLASS_LOCATOR_EXCEPTION: str     # '0F'
153
CLASS_INVALID_GRANTOR: str       # '0L'
154
CLASS_INVALID_ROLE_SPECIFICATION: str  # '0P'
155
CLASS_DIAGNOSTICS_EXCEPTION: str # '0Z'
156
CLASS_CASE_NOT_FOUND: str        # '20'
157
CLASS_CARDINALITY_VIOLATION: str # '21'
158
CLASS_DATA_EXCEPTION: str        # '22'
159
CLASS_INTEGRITY_CONSTRAINT_VIOLATION: str  # '23'
160
CLASS_INVALID_CURSOR_STATE: str  # '24'
161
CLASS_INVALID_TRANSACTION_STATE: str  # '25'
162
CLASS_INVALID_SQL_STATEMENT_NAME: str  # '26'
163
CLASS_TRIGGERED_DATA_CHANGE_VIOLATION: str  # '27'
164
CLASS_INVALID_AUTHORIZATION_SPECIFICATION: str  # '28'
165
CLASS_DEPENDENT_PRIVILEGE_DESCRIPTORS_STILL_EXIST: str  # '2B'
166
CLASS_INVALID_TRANSACTION_TERMINATION: str  # '2D'
167
CLASS_SQL_ROUTINE_EXCEPTION: str # '2F'
168
CLASS_INVALID_CURSOR_NAME: str   # '34'
169
CLASS_EXTERNAL_ROUTINE_EXCEPTION: str  # '38'
170
CLASS_EXTERNAL_ROUTINE_INVOCATION_EXCEPTION: str  # '39'
171
CLASS_SAVEPOINT_EXCEPTION: str   # '3B'
172
CLASS_INVALID_CATALOG_NAME: str  # '3D'
173
CLASS_INVALID_SCHEMA_NAME: str   # '3F'
174
CLASS_TRANSACTION_ROLLBACK: str  # '40'
175
CLASS_SYNTAX_ERROR_OR_ACCESS_RULE_VIOLATION: str  # '42'
176
CLASS_WITH_CHECK_OPTION_VIOLATION: str  # '44'
177
CLASS_INSUFFICIENT_RESOURCES: str  # '53'
178
CLASS_PROGRAM_LIMIT_EXCEEDED: str  # '54'
179
CLASS_OBJECT_NOT_IN_PREREQUISITE_STATE: str  # '55'
180
CLASS_OPERATOR_INTERVENTION: str  # '57'
181
CLASS_SYSTEM_ERROR: str          # '58'
182
CLASS_SNAPSHOT_FAILURE: str      # '72'
183
CLASS_CONFIGURATION_FILE_ERROR: str  # 'F0'
184
CLASS_FOREIGN_DATA_WRAPPER_ERROR: str  # 'HV'
185
CLASS_PL_PGSQL_ERROR: str        # 'P0'
186
CLASS_INTERNAL_ERROR: str        # 'XX'
187

188
# Specific error constants (5-character codes)
189
SUCCESSFUL_COMPLETION: str        # '00000'
190
WARNING: str                      # '01000'
191
DYNAMIC_RESULT_SETS_RETURNED: str # '0100C'
192
IMPLICIT_ZERO_BIT_PADDING: str    # '01008'
193
NULL_VALUE_ELIMINATED_IN_SET_FUNCTION: str  # '01003'
194
PRIVILEGE_NOT_GRANTED: str        # '01007'
195
PRIVILEGE_NOT_REVOKED: str        # '01006'
196
STRING_DATA_RIGHT_TRUNCATION: str # '01004'
197
DEPRECATED_FEATURE: str           # '01P01'
198
NO_DATA: str                      # '02000'
199
NO_ADDITIONAL_DYNAMIC_RESULT_SETS_RETURNED: str  # '02001'
200
SQL_STATEMENT_NOT_YET_COMPLETE: str  # '03000'
201
CONNECTION_EXCEPTION: str         # '08000'
202
CONNECTION_DOES_NOT_EXIST: str    # '08003'
203
CONNECTION_FAILURE: str           # '08006'
204
SQLCLIENT_UNABLE_TO_ESTABLISH_SQLCONNECTION: str  # '08001'
205
SQLSERVER_REJECTED_ESTABLISHMENT_OF_SQLCONNECTION: str  # '08004'
206
TRANSACTION_RESOLUTION_UNKNOWN: str  # '08007'
207
PROTOCOL_VIOLATION: str           # '08P01'
208
TRIGGERED_ACTION_EXCEPTION: str   # '09000'
209
FEATURE_NOT_SUPPORTED: str        # '0A000'
210
INVALID_TRANSACTION_INITIATION: str  # '0B000'
211
LOCATOR_EXCEPTION: str            # '0F000'
212
INVALID_LOCATOR_SPECIFICATION: str  # '0F001'
213
INVALID_GRANTOR: str              # '0L000'
214
INVALID_GRANT_OPERATION: str      # '0LP01'
215
INVALID_ROLE_SPECIFICATION: str   # '0P000'
216
DIAGNOSTICS_EXCEPTION: str        # '0Z000'
217
STACKED_DIAGNOSTICS_ACCESSED_WITHOUT_ACTIVE_HANDLER: str  # '0Z002'
218
CASE_NOT_FOUND: str               # '20000'
219
CARDINALITY_VIOLATION: str        # '21000'
220
DATA_EXCEPTION: str               # '22000'
221
ARRAY_SUBSCRIPT_ERROR: str        # '2202E'
222
CHARACTER_NOT_IN_REPERTOIRE: str  # '22021'
223
DATETIME_FIELD_OVERFLOW: str      # '22008'
224
DIVISION_BY_ZERO: str             # '22012'
225
ERROR_IN_ASSIGNMENT: str          # '22005'
226
ESCAPE_CHARACTER_CONFLICT: str    # '2200B'
227
INDICATOR_OVERFLOW: str           # '22022'
228
INTERVAL_FIELD_OVERFLOW: str      # '22015'
229
INVALID_ARGUMENT_FOR_LOGARITHM: str  # '2201E'
230
INVALID_ARGUMENT_FOR_NTILE_FUNCTION: str  # '22014'
231
INVALID_ARGUMENT_FOR_NTH_VALUE_FUNCTION: str  # '22016'
232
INVALID_ARGUMENT_FOR_POWER_FUNCTION: str  # '2201F'
233
INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION: str  # '2201G'
234
INVALID_CHARACTER_VALUE_FOR_CAST: str  # '22018'
235
INVALID_DATETIME_FORMAT: str      # '22007'
236
INVALID_ESCAPE_CHARACTER: str     # '22019'
237
INVALID_ESCAPE_OCTET: str         # '2200D'
238
INVALID_ESCAPE_SEQUENCE: str      # '22025'
239
NONSTANDARD_USE_OF_ESCAPE_CHARACTER: str  # '22P06'
240
INVALID_INDICATOR_PARAMETER_VALUE: str  # '22010'
241
INVALID_PARAMETER_VALUE: str      # '22023'
242
INVALID_REGULAR_EXPRESSION: str   # '2201B'
243
INVALID_ROW_COUNT_IN_LIMIT_CLAUSE: str  # '2201W'
244
INVALID_ROW_COUNT_IN_RESULT_OFFSET_CLAUSE: str  # '2201X'
245
INVALID_TABLESAMPLE_ARGUMENT: str # '2202H'
246
INVALID_TABLESAMPLE_REPEAT: str   # '2202G'
247
INVALID_TIME_ZONE_DISPLACEMENT_VALUE: str  # '22009'
248
INVALID_USE_OF_ESCAPE_CHARACTER: str  # '2200C'
249
MOST_SPECIFIC_TYPE_MISMATCH: str  # '2200G'
250
NULL_VALUE_NOT_ALLOWED: str       # '22004'
251
NULL_VALUE_NO_INDICATOR_PARAMETER: str  # '22002'
252
NUMERIC_VALUE_OUT_OF_RANGE: str   # '22003'
253
STRING_DATA_LENGTH_MISMATCH: str  # '22026'
254
STRING_DATA_RIGHT_TRUNCATION: str # '22001'
255
SUBSTRING_ERROR: str              # '22011'
256
TRIM_ERROR: str                   # '22027'
257
UNTERMINATED_C_STRING: str        # '22024'
258
ZERO_LENGTH_CHARACTER_STRING: str # '2200F'
259
FLOATING_POINT_EXCEPTION: str     # '22P01'
260
INVALID_TEXT_REPRESENTATION: str  # '22P02'
261
INVALID_BINARY_REPRESENTATION: str  # '22P03'
262
BAD_COPY_FILE_FORMAT: str         # '22P04'
263
UNTRANSLATABLE_CHARACTER: str     # '22P05'
264
NOT_AN_XML_DOCUMENT: str          # '2200L'
265
INVALID_XML_DOCUMENT: str         # '2200M'
266
INVALID_XML_CONTENT: str          # '2200N'
267
INVALID_XML_COMMENT: str          # '2200S'
268
INVALID_XML_PROCESSING_INSTRUCTION: str  # '2200T'
269
INTEGRITY_CONSTRAINT_VIOLATION: str  # '23000'
270
RESTRICT_VIOLATION: str           # '23001'
271
NOT_NULL_VIOLATION: str           # '23502'
272
FOREIGN_KEY_VIOLATION: str        # '23503'
273
UNIQUE_VIOLATION: str             # '23505'
274
CHECK_VIOLATION: str              # '23514'
275
EXCLUSION_VIOLATION: str          # '23P01'
276
INVALID_CURSOR_STATE: str         # '24000'
277
INVALID_TRANSACTION_STATE: str    # '25000'
278
ACTIVE_SQL_TRANSACTION: str       # '25001'
279
BRANCH_TRANSACTION_ALREADY_ACTIVE: str  # '25002'
280
HELD_CURSOR_REQUIRES_SAME_ISOLATION_LEVEL: str  # '25008'
281
INAPPROPRIATE_ACCESS_MODE_FOR_BRANCH_TRANSACTION: str  # '25003'
282
INAPPROPRIATE_ISOLATION_LEVEL_FOR_BRANCH_TRANSACTION: str  # '25004'
283
NO_ACTIVE_SQL_TRANSACTION_FOR_BRANCH_TRANSACTION: str  # '25005'
284
READ_ONLY_SQL_TRANSACTION: str    # '25006'
285
SCHEMA_AND_DATA_STATEMENT_MIXING_NOT_SUPPORTED: str  # '25007'
286
NO_ACTIVE_SQL_TRANSACTION: str    # '25P01'
287
IN_FAILED_SQL_TRANSACTION: str    # '25P02'
288
INVALID_SQL_STATEMENT_NAME: str   # '26000'
289
TRIGGERED_DATA_CHANGE_VIOLATION: str  # '27000'
290
INVALID_AUTHORIZATION_SPECIFICATION: str  # '28000'
291
INVALID_PASSWORD: str             # '28P01'
292
DEPENDENT_PRIVILEGE_DESCRIPTORS_STILL_EXIST: str  # '2B000'
293
DEPENDENT_OBJECTS_STILL_EXIST: str  # '2BP01'
294
INVALID_TRANSACTION_TERMINATION: str  # '2D000'
295
SQL_ROUTINE_EXCEPTION: str        # '2F000'
296
FUNCTION_EXECUTED_NO_RETURN_STATEMENT: str  # '2F005'
297
MODIFYING_SQL_DATA_NOT_PERMITTED: str  # '2F002'
298
PROHIBITED_SQL_STATEMENT_ATTEMPTED: str  # '2F003'
299
READING_SQL_DATA_NOT_PERMITTED: str  # '2F004'
300
INVALID_CURSOR_NAME: str          # '34000'
301
EXTERNAL_ROUTINE_EXCEPTION: str   # '38000'
302
CONTAINING_SQL_NOT_PERMITTED: str # '38001'
303
MODIFYING_SQL_DATA_NOT_PERMITTED: str  # '38002'
304
PROHIBITED_SQL_STATEMENT_ATTEMPTED: str  # '38003'
305
READING_SQL_DATA_NOT_PERMITTED: str  # '38004'
306
EXTERNAL_ROUTINE_INVOCATION_EXCEPTION: str  # '39000'
307
INVALID_SQLSTATE_RETURNED: str    # '39001'
308
NULL_VALUE_NOT_ALLOWED: str       # '39004'
309
TRIGGER_PROTOCOL_VIOLATED: str    # '39P01'
310
SRF_PROTOCOL_VIOLATED: str        # '39P02'
311
EVENT_TRIGGER_PROTOCOL_VIOLATED: str  # '39P03'
312
SAVEPOINT_EXCEPTION: str          # '3B000'
313
INVALID_SAVEPOINT_SPECIFICATION: str  # '3B001'
314
INVALID_CATALOG_NAME: str         # '3D000'
315
INVALID_SCHEMA_NAME: str          # '3F000'
316
TRANSACTION_ROLLBACK: str         # '40000'
317
TRANSACTION_INTEGRITY_CONSTRAINT_VIOLATION: str  # '40002'
318
SERIALIZATION_FAILURE: str        # '40001'
319
STATEMENT_COMPLETION_UNKNOWN: str # '40003'
320
DEADLOCK_DETECTED: str            # '40P01'
321
SYNTAX_ERROR_OR_ACCESS_RULE_VIOLATION: str  # '42000'
322
SYNTAX_ERROR: str                 # '42601'
323
INSUFFICIENT_PRIVILEGE: str       # '42501'
324
CANNOT_COERCE: str                # '42846'
325
GROUPING_ERROR: str               # '42803'
326
WINDOWING_ERROR: str              # '42P20'
327
INVALID_RECURSION: str            # '42P19'
328
INVALID_FOREIGN_KEY: str          # '42830'
329
INVALID_NAME: str                 # '42602'
330
NAME_TOO_LONG: str               # '42622'
331
RESERVED_NAME: str               # '42939'
332
DATATYPE_MISMATCH: str           # '42804'
333
INDETERMINATE_DATATYPE: str      # '42P18'
334
COLLATION_MISMATCH: str          # '42P21'
335
INDETERMINATE_COLLATION: str     # '42P22'
336
WRONG_OBJECT_TYPE: str           # '42809'
337
GENERATED_ALWAYS: str            # '428C9'
338
UNDEFINED_COLUMN: str            # '42703'
339
UNDEFINED_FUNCTION: str          # '42883'
340
UNDEFINED_TABLE: str             # '42P01'
341
UNDEFINED_PARAMETER: str         # '42P02'
342
UNDEFINED_OBJECT: str            # '42704'
343
DUPLICATE_COLUMN: str            # '42701'
344
DUPLICATE_CURSOR: str            # '42P03'
345
DUPLICATE_DATABASE: str          # '42P04'
346
DUPLICATE_FUNCTION: str          # '42723'
347
DUPLICATE_PREPARED_STATEMENT: str # '42P05'
348
DUPLICATE_SCHEMA: str            # '42P06'
349
DUPLICATE_TABLE: str             # '42P07'
350
DUPLICATE_ALIAS: str             # '42712'
351
DUPLICATE_OBJECT: str            # '42710'
352
AMBIGUOUS_COLUMN: str            # '42702'
353
AMBIGUOUS_FUNCTION: str          # '42725'
354
AMBIGUOUS_PARAMETER: str         # '42P08'
355
AMBIGUOUS_ALIAS: str             # '42P09'
356
INVALID_COLUMN_REFERENCE: str    # '42P10'
357
INVALID_COLUMN_DEFINITION: str   # '42611'
358
INVALID_CURSOR_DEFINITION: str   # '42P11'
359
INVALID_DATABASE_DEFINITION: str # '42P12'
360
INVALID_FUNCTION_DEFINITION: str # '42P13'
361
INVALID_PREPARED_STATEMENT_DEFINITION: str  # '42P14'
362
INVALID_SCHEMA_DEFINITION: str   # '42P15'
363
INVALID_TABLE_DEFINITION: str    # '42P16'
364
INVALID_OBJECT_DEFINITION: str   # '42P17'
365
WITH_CHECK_OPTION_VIOLATION: str # '44000'
366
INSUFFICIENT_RESOURCES: str      # '53000'
367
DISK_FULL: str                   # '53100'
368
OUT_OF_MEMORY: str              # '53200'
369
TOO_MANY_CONNECTIONS: str       # '53300'
370
CONFIGURATION_LIMIT_EXCEEDED: str # '53400'
371
PROGRAM_LIMIT_EXCEEDED: str      # '54000'
372
STATEMENT_TOO_COMPLEX: str       # '54001'
373
TOO_MANY_COLUMNS: str           # '54011'
374
TOO_MANY_ARGUMENTS: str         # '54023'
375
OBJECT_NOT_IN_PREREQUISITE_STATE: str  # '55000'
376
OBJECT_IN_USE: str              # '55006'
377
CANT_CHANGE_RUNTIME_PARAM: str  # '55P02'
378
LOCK_NOT_AVAILABLE: str         # '55P03'
379
OPERATOR_INTERVENTION: str       # '57000'
380
QUERY_CANCELED: str             # '57014'
381
ADMIN_SHUTDOWN: str             # '57P01'
382
CRASH_SHUTDOWN: str             # '57P02'
383
CANNOT_CONNECT_NOW: str         # '57P03'
384
DATABASE_DROPPED: str           # '57P04'
385
SYSTEM_ERROR: str               # '58000'
386
IO_ERROR: str                   # '58030'
387
UNDEFINED_FILE: str             # '58P01'
388
DUPLICATE_FILE: str             # '58P02'
389
SNAPSHOT_TOO_OLD: str           # '72000'
390
CONFIG_FILE_ERROR: str          # 'F0000'
391
LOCK_FILE_EXISTS: str           # 'F0001'
392
FDW_ERROR: str                  # 'HV000'
393
FDW_COLUMN_NAME_NOT_FOUND: str  # 'HV005'
394
FDW_DYNAMIC_PARAMETER_VALUE_NEEDED: str  # 'HV002'
395
FDW_FUNCTION_SEQUENCE_ERROR: str # 'HV010'
396
FDW_INCONSISTENT_DESCRIPTOR_INFORMATION: str  # 'HV021'
397
FDW_INVALID_ATTRIBUTE_VALUE: str # 'HV024'
398
FDW_INVALID_COLUMN_NAME: str    # 'HV007'
399
FDW_INVALID_COLUMN_NUMBER: str  # 'HV008'
400
FDW_INVALID_DATA_TYPE: str      # 'HV004'
401
FDW_INVALID_DATA_TYPE_DESCRIPTORS: str  # 'HV006'
402
FDW_INVALID_DESCRIPTOR_FIELD_IDENTIFIER: str  # 'HV091'
403
FDW_INVALID_HANDLE: str         # 'HV00B'
404
FDW_INVALID_OPTION_INDEX: str   # 'HV00C'
405
FDW_INVALID_OPTION_NAME: str    # 'HV00D'
406
FDW_INVALID_STRING_LENGTH_OR_BUFFER_LENGTH: str  # 'HV090'
407
FDW_INVALID_STRING_FORMAT: str  # 'HV00A'
408
FDW_INVALID_USE_OF_NULL_POINTER: str  # 'HV009'
409
FDW_TOO_MANY_HANDLES: str       # 'HV014'
410
FDW_OUT_OF_MEMORY: str          # 'HV001'
411
FDW_NO_SCHEMAS: str             # 'HV00P'
412
FDW_OPTION_NAME_NOT_FOUND: str  # 'HV00J'
413
FDW_REPLY_HANDLE: str           # 'HV00K'
414
FDW_SCHEMA_NOT_FOUND: str       # 'HV00Q'
415
FDW_TABLE_NOT_FOUND: str        # 'HV00R'
416
FDW_UNABLE_TO_CREATE_EXECUTION: str  # 'HV00L'
417
FDW_UNABLE_TO_CREATE_REPLY: str # 'HV00M'
418
FDW_UNABLE_TO_ESTABLISH_CONNECTION: str  # 'HV00N'
419
PLPGSQL_ERROR: str              # 'P0000'
420
RAISE_EXCEPTION: str            # 'P0001'
421
NO_DATA_FOUND: str              # 'P0002'
422
TOO_MANY_ROWS: str              # 'P0003'
423
ASSERT_FAILURE: str             # 'P0004'
424
INTERNAL_ERROR: str             # 'XX000'
425
DATA_CORRUPTED: str             # 'XX001'
426
INDEX_CORRUPTED: str            # 'XX002'
427
```
428

429
**Usage Example:**
430

431
```python
432
from psycopg2.errorcodes import (
433
    UNIQUE_VIOLATION, NOT_NULL_VIOLATION, FOREIGN_KEY_VIOLATION,
434
    UNDEFINED_TABLE, SYNTAX_ERROR, INSUFFICIENT_PRIVILEGE
435
)
436
import psycopg2
437

438
conn = psycopg2.connect("host=localhost dbname=mydb user=myuser password=mypass")
439

440
try:
441
    with conn.cursor() as cur:
442
        cur.execute("INSERT INTO users (email) VALUES (%s)", ("duplicate@email.com",))
443
        conn.commit()
444
        
445
except psycopg2.IntegrityError as e:
446
    if e.pgcode == UNIQUE_VIOLATION:
447
        print("Email already exists")
448
    elif e.pgcode == NOT_NULL_VIOLATION:
449
        print("Required field is missing")
450
    elif e.pgcode == FOREIGN_KEY_VIOLATION:
451
        print("Referenced record does not exist")
452
    else:
453
        print(f"Other integrity error: {e}")
454
    conn.rollback()
455

456
try:
457
    with conn.cursor() as cur:
458
        cur.execute("SELECT * FROM nonexistent_table")
459
        
460
except psycopg2.ProgrammingError as e:
461
    if e.pgcode == UNDEFINED_TABLE:
462
        print("Table does not exist")
463
    elif e.pgcode == SYNTAX_ERROR:
464
        print("SQL syntax error")
465
    elif e.pgcode == INSUFFICIENT_PRIVILEGE:
466
        print("Permission denied")
467
    else:
468
        print(f"Other programming error: {e}")
469

470
conn.close()
471
```
472

473
### Exception Diagnostics
474

475
Access detailed error information through exception attributes and diagnostics.
476

477
```python { .api }
478
class Diagnostics:
479
    """Exception diagnostics object."""
480
    
481
    @property
482
    def column_name(self):
483
        """Column name related to error."""
484
    
485
    @property
486
    def constraint_name(self):
487
        """Constraint name that was violated."""
488
    
489
    @property
490
    def context(self):  
491
        """Error context information."""
492
    
493
    @property
494
    def datatype_name(self):
495
        """Data type name related to error."""
496
    
497
    @property
498
    def internal_position(self):
499
        """Internal error position."""
500
    
501
    @property
502
    def internal_query(self):
503
        """Internal query that caused error."""
504
    
505
    @property
506
    def message_detail(self):
507
        """Detailed error message."""
508
    
509
    @property
510
    def message_hint(self):
511
        """Error message hint."""
512
    
513
    @property
514
    def message_primary(self):
515
        """Primary error message."""
516
    
517
    @property
518
    def schema_name(self):
519
        """Schema name related to error."""
520
    
521
    @property
522
    def severity(self):
523
        """Error severity level."""
524
    
525
    @property
526
    def source_file(self):
527
        """Source file where error occurred."""
528
    
529
    @property
530
    def source_function(self):
531
        """Source function where error occurred."""
532
    
533
    @property
534
    def source_line(self):
535
        """Source line where error occurred."""
536
    
537
    @property
538
    def sqlstate(self):
539
        """SQLSTATE error code."""
540
    
541
    @property
542
    def statement_position(self):
543
        """Position in statement where error occurred."""
544
    
545
    @property
546
    def table_name(self):
547
        """Table name related to error."""
548
```
549

550
**Usage Example:**
551

552
```python
553
import psycopg2
554

555
conn = psycopg2.connect("host=localhost dbname=mydb user=myuser password=mypass")
556

557
try:
558
    with conn.cursor() as cur:
559
        cur.execute("INSERT INTO users (email, age) VALUES (%s, %s)", 
560
                   ("user@example.com", "invalid_age"))
561
        conn.commit()
562
        
563
except psycopg2.DataError as e:
564
    print(f"Error: {e}")
565
    print(f"SQLSTATE: {e.pgcode}")
566
    
567
    # Access diagnostics if available
568
    if hasattr(e, 'diag'):
569
        diag = e.diag
570
        print(f"Severity: {diag.severity}")
571
        print(f"Primary message: {diag.message_primary}")
572
        print(f"Detail: {diag.message_detail}")
573
        print(f"Hint: {diag.message_hint}")
574
        print(f"Position: {diag.statement_position}")
575
        print(f"Context: {diag.context}")
576
        
577
        if diag.table_name:
578
            print(f"Table: {diag.table_name}")
579
        if diag.column_name:
580
            print(f"Column: {diag.column_name}")
581
        if diag.constraint_name:
582
            print(f"Constraint: {diag.constraint_name}")
583
    
584
    conn.rollback()
585

586
conn.close()
587
```
588

589
### Error Handling Patterns
590

591
Common patterns for robust error handling in applications.
592

593
**Usage Example:**
594

595
```python
596
import psycopg2
597
from psycopg2.errorcodes import SERIALIZATION_FAILURE, DEADLOCK_DETECTED
598
import time
599
import random
600

601
def retry_on_serialization_failure(func, max_retries=3, base_delay=0.1):
602
    """Retry function on serialization failures with exponential backoff."""
603
    for attempt in range(max_retries):
604
        try:
605
            return func()
606
        except psycopg2.OperationalError as e:
607
            if e.pgcode in (SERIALIZATION_FAILURE, DEADLOCK_DETECTED):
608
                if attempt < max_retries - 1:
609
                    delay = base_delay * (2 ** attempt) + random.uniform(0, 0.1)
610
                    print(f"Serialization failure, retrying in {delay:.2f}s...")
611
                    time.sleep(delay)
612
                    continue
613
            raise
614

615
def safe_database_operation():
616
    """Example database operation with comprehensive error handling."""
617
    conn = psycopg2.connect("host=localhost dbname=mydb user=myuser password=mypass")
618
    
619
    def transfer_money():
620
        with conn.cursor() as cur:
621
            # Set serializable isolation level
622
            conn.set_isolation_level(3)  # ISOLATION_LEVEL_SERIALIZABLE
623
            
624
            cur.execute("SELECT balance FROM accounts WHERE id = %s", (1,))
625
            from_balance = cur.fetchone()[0]
626
            
627
            cur.execute("SELECT balance FROM accounts WHERE id = %s", (2,))
628
            to_balance = cur.fetchone()[0]
629
            
630
            if from_balance < 100:
631
                raise ValueError("Insufficient funds")
632
            
633
            cur.execute("UPDATE accounts SET balance = balance - 100 WHERE id = %s", (1,))
634
            cur.execute("UPDATE accounts SET balance = balance + 100 WHERE id = %s", (2,))
635
            
636
            conn.commit()
637
            print("Transfer completed successfully")
638
    
639
    try:
640
        retry_on_serialization_failure(transfer_money)
641
        
642
    except psycopg2.IntegrityError as e:
643
        print(f"Integrity constraint violation: {e}")
644
        conn.rollback()
645
        
646
    except psycopg2.ProgrammingError as e:
647
        print(f"Programming error: {e}")
648
        conn.rollback()
649
        
650
    except psycopg2.OperationalError as e:
651
        print(f"Operational error: {e}")
652
        if conn:
653
            conn.rollback()
654
            
655
    except ValueError as e:
656
        print(f"Business logic error: {e}")
657
        conn.rollback()
658
        
659
    except Exception as e:
660
        print(f"Unexpected error: {e}")
661
        if conn:
662
            conn.rollback()
663
        raise
664
        
665
    finally:
666
        if conn:
667
            conn.close()
668

669
# Context manager for automatic error handling
670
class DatabaseTransaction:
671
    """Context manager for database transactions with automatic error handling."""
672
    
673
    def __init__(self, connection_params):
674
        self.connection_params = connection_params
675
        self.conn = None
676
    
677
    def __enter__(self):
678
        self.conn = psycopg2.connect(**self.connection_params)
679
        return self.conn
680
    
681
    def __exit__(self, exc_type, exc_val, exc_tb):
682
        if exc_type is None:
683
            # No exception, commit transaction
684
            self.conn.commit()
685
        else:
686
            # Exception occurred, rollback transaction
687
            self.conn.rollback()
688
            
689
            # Log different types of errors
690
            if isinstance(exc_val, psycopg2.IntegrityError):
691
                print(f"Data integrity error: {exc_val}")
692
            elif isinstance(exc_val, psycopg2.ProgrammingError):
693
                print(f"SQL programming error: {exc_val}")
694
            elif isinstance(exc_val, psycopg2.OperationalError):
695
                print(f"Database operational error: {exc_val}")
696
            else:
697
                print(f"Database error: {exc_val}")
698
        
699
        self.conn.close()
700
        return False  # Don't suppress exceptions
701

702
# Usage of context manager
703
try:
704
    with DatabaseTransaction({'host': 'localhost', 'database': 'mydb', 'user': 'myuser', 'password': 'mypass'}) as conn:
705
        with conn.cursor() as cur:
706
            cur.execute("INSERT INTO users (name, email) VALUES (%s, %s)", 
707
                       ("Alice", "alice@example.com"))
708
            cur.execute("INSERT INTO profiles (user_id, bio) VALUES (currval('users_id_seq'), %s)",
709
                       ("Software engineer",))
710
        # Transaction automatically committed if no exceptions
711
        
712
except Exception as e:
713
    print(f"Transaction failed: {e}")
714
```
715

716
## Types
717

718
### Exception Hierarchy
719

720
```python { .api }
721
class Error(Exception):
722
    """Base psycopg2 exception."""
723
    
724
    pgcode: str | None  # PostgreSQL SQLSTATE code
725
    pgerror: str | None  # PostgreSQL error message
726
    diag: Diagnostics | None  # Detailed diagnostics
727

728
class Warning(Exception):
729
    """Database warning."""
730

731
class InterfaceError(Error):
732
    """Interface-related errors."""
733

734
class DatabaseError(Error):
735
    """Database engine errors."""
736

737
class DataError(DatabaseError):
738
    """Data-related errors."""
739

740
class OperationalError(DatabaseError):
741
    """Operation-related errors."""
742

743
class IntegrityError(DatabaseError):  
744
    """Database integrity violations."""
745

746
class InternalError(DatabaseError):
747
    """Database internal errors."""
748

749
class ProgrammingError(DatabaseError):
750
    """SQL programming errors."""
751

752
class NotSupportedError(DatabaseError):
753
    """Unsupported operations."""
754

755
class QueryCanceledError(OperationalError):
756
    """Query cancellation."""
757

758
class TransactionRollbackError(OperationalError):
759
    """Transaction rollback conditions."""
760
```
761

762
### Diagnostics Interface
763

764
```python { .api }
765
class Diagnostics:
766
    """Detailed error diagnostics."""
767
    
768
    column_name: str | None
769
    constraint_name: str | None  
770
    context: str | None
771
    datatype_name: str | None
772
    internal_position: str | None
773
    internal_query: str | None
774
    message_detail: str | None
775
    message_hint: str | None
776
    message_primary: str | None
777
    schema_name: str | None
778
    severity: str | None
779
    source_file: str | None
780
    source_function: str | None
781
    source_line: str | None
782
    sqlstate: str | None
783
    statement_position: str | None
784
    table_name: str | None
785
```
786

787
### Error Lookup Functions
788

789
```python { .api }
790
def lookup(code: str) -> type:
791
    """Look up exception class by SQLSTATE code."""
792

793
# Error code constants (over 200 specific codes)
794
UNIQUE_VIOLATION: str          # '23505'
795
NOT_NULL_VIOLATION: str        # '23502'
796
FOREIGN_KEY_VIOLATION: str     # '23503'
797
CHECK_VIOLATION: str           # '23514'
798
UNDEFINED_TABLE: str           # '42P01'
799
SYNTAX_ERROR: str              # '42601'
800
INSUFFICIENT_PRIVILEGE: str    # '42501'
801
DEADLOCK_DETECTED: str         # '40P01'
802
SERIALIZATION_FAILURE: str     # '40001'
803
# ... and many more
804
```