PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1050 queries.

  • All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
  • Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets it shouldn't be a problem in case of relatively small amount of data, which is in the system catalog of a database.
  • Statistics about the catalog content and project home in GitHub that has additional information.

# Name Goal Type Data source ▲ Last update License
601 Perhaps is not snake_case - id, code, key, or nr is not preceded by an underscore Find names that perhaps do not use the snake_case naming style because the name ends with the phrase "id", "uuid", "code", "kood", "key", or "nr" that is not preceded by an underscore. Prefer snake_case over PascalCase and camelCase in names. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
602 Perhaps is not snake_case - long subsections without underscores Find names that perhaps do not use the snake_case naming style because the name contains a long subsection (at least 20 characters) without underscores. Prefer snake_case over PascalCase and camelCase in names. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
603 Perhaps last update time trigger is missing Find base tables that have a column for last update time but the table does not have associated before update row level trigger for changing the last update time. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
604 Perhaps multiple surrogate keys This query identifies base tables with a potentially redundant key structure. It specifically targets those having more than one PRIMARY KEY or UNIQUE constraint defined on a single integer-type column whose name matches the surrogate key naming convention (id_* or *_id). This pattern is a strong indicator of multiple surrogate keys for the same entity, which can indicate an overcomplicated data model. A single entity should typically have only one system-generated identifier to maintain schema clarity, simplify join logic, and avoid redundancy. The presence of multiple such keys warrants a review to determine if one is superfluous. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-15 11:37 MIT License View
605 Perhaps searching based on a name instead of a code Find derived tables with a search condition that is possible based on a name instead of a code. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
606 Perhaps spaces are unnecessarily restricted Find base table columns that name refers to the possibility that the column is used to record names or textual descriptions but the column seems to have a simple check constraint that restricts spaces in these. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
607 Perhaps the name referes to multiple concepts Find database objects that name contains words "and" (English) or "ja" (Estonian). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
608 Perhaps the type of a base table column/domain should be BOOLEAN (based on CHECK constraints) This query identifies base table columns and domains that utilize CHECK constraints to simulate boolean logic on non-boolean data types. It targets constraints that restrict the domain of permitted values to binary sets, such as {0, 1}, {'Y', 'N'}, {'T', 'F'}, or string literals like 'true'/'false'. While functional, this "pseudo-boolean" pattern is considered suboptimal in PostgreSQL. The native BOOLEAN data type is preferred for its storage efficiency, semantic clarity, and built-in support for logical operators, rendering such manual constraints unnecessary. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-14 14:37 MIT License View
609 Perhaps the type of a base table column should be BOOLEAN (based on column names) This query identifies a semantic mismatch between a column's name and its data type. It flags base table columns that adhere to a predicate-based naming convention (i.e., starting with is_, has_, can_, or on_) but are not defined with the BOOLEAN data type. This is a design flaw as it forces developers to infer and manage truthiness through other types (e.g., INTEGER, CHAR(1)), which undermines schema clarity, requires data type coercion in queries, and can compromise data integrity by permitting non-boolean states. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-13 14:07 MIT License View
610 Perhaps the type of a base table column should be BOOLEAN (based on enumerated types) Find base table columns that have an enumerated type that seems to emulate Boolean type. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
611 Perhaps the type of a parameter should be BOOLEAN (based on parameter names) Find routine parameters that based on the name seem to hold truth values. Find parameters that name starts with "is_" or "has_" or "can_" or "on_" and that do not have Boolean type. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
612 Perhaps too many different prefixes in the names of database objects that have the same type One should be consistent in naming, including in the use of prefixes. If you use prefix in the name of a database object, then it should refer to the type of the database object. Do not use different prefixes in the names of database objects that have the same type. Find types of database objects in case of which there are different prefixes in different names. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
613 Perhaps too many different suffixes in the names of database objects that have the same type One should be consistent in naming, including in the use of suffixes. If you use sufix in the name of a database object, then it should refer to the type of the database object. Do not use different suffixes in the names of database objects that have the same type. Find types of database objects in case of which there are different suffixes in different names. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
614 Perhaps too many input parameters Too many parameters (in this case four or more) could be a sign of not separating concerns and having a routine that has more than one task. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
615 Perhaps too many square brackets Character classes are surrounded by two pairs of square brackets. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
616 Perhaps unnecessary DECLARE section in a PL/pgSQL routine Find PL/pgSQL routines that perhaps unnecessarily contain DECLARE section. More specifically, find routines with the DECLARE section where the only task seems to be raising an exception. The query excludes the cases where the error message is constructed dynamically, i.e., in this case using a variable maybe justifiable. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
617 Perhaps unnecessary DECLARE section in a PL/pgSQL routine (2) Find PL/pgSQL routines that perhaps unnecessarily contain DECLARE section. More specifically, find routines with the DECLARE section where the keyword DECLARE is followed by BEGIN, i.e., the DECLARE section is empty. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
618 Perhaps updating of modification time is missing Find routines with SQL-standard body that seem to update data in a table that has a column for modification time but the routine does not seem to update the modification time while updating the row and the table does not seem to have an UPDATE trigger that changes the modification time. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
619 Perhaps USING syntax could be used for joining in the subqueries of derived tables This query identifies derived tables (views) that utilize the explicit ANSI SQL-92 join syntax with the ON clause. It specifically targets views where join conditions are defined within the FROM clause but explicitly excludes those using the simplified USING syntax. This helps to identify derived tables that subquery could be simplified. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-03 15:57 MIT License View
620 Phone number columns lacking digit validation constraints This query identifies non-foreign key base table columns intended for telephone number storage that lack essential data validation. It targets columns whose names imply phone data (e.g., containing "phone", "tel") but which have no associated simple CHECK constraint validating the presence of numeric digits. Without such a constraint (e.g., a regex check for [0-9]), the column allows for invalid entries such as purely alphabetic strings or email addresses, compromising data integrity. The query assumes that a valid phone number must minimally contain digits. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-27 10:39 MIT License View