| Goal | This query identifies PL/pgSQL routines that invoke RAISE EXCEPTION and have malformed, generic, or improperly classified ERRCODE values in RAISE EXCEPTION statements. It acts as a linter for PostgreSQL error handling best practices. Specifically, it flags generic codes (like P0001 or raise_exception), the misuse of warning/info classes (00-02) as breaking exceptions, and the hijacking of reserved standard error classes. It also ensures that custom error codes follow the correct 5-character format and start with a digit between 5 and 9, thereby preventing conflicts with built-in PostgreSQL errors. |
|---|---|
| Type | Problem detection Each row in the result could represent a flaw in the design |
| Reliability | High Few or no false-positive results |
| License | MIT (opens in new tab) |
| Data Source | INFORMATION_SCHEMA+system catalog |
| SQL Query |
|
Fixing Suggestion
To resolve this issue, review the flagged ERRCODE and update it according to PostgreSQL standards:
- Avoid the generic P0001; create a specific error code for your application logic.
- If throwing a built-in PostgreSQL error, use its lowercase text alias (e.g., unique_violation) for better readability instead of its alphanumeric code.
- For custom business logic exceptions, define a dedicated 5-character SQLSTATE code starting with a digit between 5 and 9 (e.g., 50001) to avoid hijacking reserved system classes.
- Ensure the error class matches the severity (do not use 00, 01, or 02 for breaking exceptions).
Collections
This query belongs to the following collections:
Find problems automatically
Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
| Name | Description |
|---|---|
| Find problems automatically | Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not . |
Categories
This query is classified under the following categories:
Triggers and rules
Queries of this category provide information about triggers and rules in a database.
User-defined routines
Queries of this category provide information about the user-defined routines
| Name | Description |
|---|---|
| Triggers and rules | Queries of this category provide information about triggers and rules in a database. |
| User-defined routines | Queries of this category provide information about the user-defined routines |