| Goal | This query identifies exclusion constraints that apply the upper() function to a column. In PostgreSQL, it is a strong community convention to standardize on the lower() function for case-insensitive constraints, indexing, and comparisons. Enforcing this convention ensures consistency across the schema and prevents logical or performance issues (for example, if a developer queries or validates data using lower(col) but the underlying exclusion constraint's index is built on upper(col)). |
|---|---|
| Type | Problem detection Each row in the result could represent a flaw in the design |
| Reliability | Medium Medium number of false-positive results |
| License | MIT (opens in new tab) |
| Data Source | system catalog only |
| SQL Query |
|
Fixing Suggestion
To resolve this issue and align with database conventions, drop the existing exclusion constraint using the ALTER TABLE ... DROP CONSTRAINT statement. Then, recreate the exclusion constraint applying the lower() function instead of upper() to the relevant columns. Ensure that any application-level validation or queries referencing these columns are updated to match.
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:
Inconsistencies
Queries of this catergory provide information about inconsistencies of solving the same problem in different places.
Performance
Queries of this category provide information about indexes in a database.
| Name | Description |
|---|---|
| Inconsistencies | Queries of this catergory provide information about inconsistencies of solving the same problem in different places. |
| Performance | Queries of this category provide information about indexes in a database. |