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
901 Cycle in a hierarchy There should not be cycles in hierarchies meaning that the parent must always be specified. In this case a parent must reference to a child or to itself, otherwise it cannot be registered. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
902 Database can be accessed through PUBLIC privileges Find as to whether the database access by users (applications) can take place thanks to PUBLIC privileges, i.e., find as to whether PUBLIC (all current and future users) has CONNECT privilege in the database. PUBLIC gets the privilege by default. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
903 Data type mismatch in check constraints This query identifies single-column CHECK constraints where the validation logic utilizes operators or functions that are incompatible with the column's native data type. It detects cases where the database must perform implicit casting to evaluate the expression (e.g., performing arithmetic on a TEXT column or string manipulation on a DATE column). Relying on implicit coercion in constraints involves unnecessary computational overhead and frequently indicates a fundamental error in data modeling or constraint formulation. Problem detection system catalog base tables only 2025-12-05 19:23 MIT License View
904 Deferrable constraints Find all deferrable constraints. General system catalog base tables only 2025-11-07 10:11 MIT License View
905 Deferrable foreign key constraint with a RESTRICT compensating action Find deferrable foreign key constraint with ON UPDATE RESTRICT or ON DELETE RESTRICT compensating action. Referential actions are carried out before, and are not part of, the checking of a referential constraint. Deferring a referential constraint defers the checking of the of the constraint (a ) but does not defer the referential actions of the referential constraint. In PostgreSQL the essential difference between NO ACTION and RESTRICT is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not. Thus RESTRICT could result with the failure of data modification where in case of NO ACTION the modification would succeed. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
906 Definition of a non-minimal superkey instead of a candidate key (based on key constraints) Find primary key, unique constraints, and exclude constraints wiht only operator = (i.e., sets of columns) that are proper subsets of other primary key, unique, and exclude constraints of the same table. Candidate key is a minimal superkey, meaning that it is not possible to remove columns from the candidate key without losing its uniqueness property. One should define primary key's and unique constraints based on candidate keys, i.e., the keys should not have redundancy in terms of columns. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
907 Definition of a non-minimal superkey instead of a candidate key (based on unique indexes) Find pairs of non-partial unique indexes where the columns of a index are a proper subset of the columns of another index. Include indexes that support a constraint (primary key, unique, exclude), i.e., these indexes have been automatically created due to the constraint declaration. Exclude the pairs where both participants have been created to support a constraint. Candidate key is a minimal superkey, meaning that it is not possible to remove columns from the candidate key without losing its uniqueness property. One should define keys based on candidate keys, i.e., the keys should not have redundancy in terms of columns. Defining a unique index essentially means defining a key in the table but it is done at the lower level of abstraction. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
908 Depth of referential tree of a schema Depth of referential tree of a database schema is the longest referential path between the tables in this schema (Piattini et al., 2001). In other words, it is the biggest value among the DRT(T) values of all the tables of the schema. Sofware measure system catalog base tables only 2025-11-07 10:11 MIT License View
909 Depth of relational tree of a table Depth of relational tree of a table T (DRT(T)) is defined by Piattini et al. (2001) as "the longest referential path between tables, from the table T to any other table in the schema". The result may help to classify the data. If the depth is 0, then probably the table contains classifers. Tables with the largest depth probably contain some extra information about main entities. Sofware measure system catalog base tables only 2025-11-07 10:11 MIT License View
910 Derived table names have prefix or suffix Find the names of views and materialized views that have prefix or suffix. Follow the same naming style as in case of base tables (derived tables are also tables). Thus, if base tables do not have prefixes or suffixes, then derived tables shouldn't have these as well. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
911 Different foreign key column names in case of referencing the same candidate key Find the cases when the names of columns in different foreign keys that reference to the same candidate key are different. If different names reflect different roles, then it is legitimate. However, there could also be accidental differences that makes it more difficult to use the database. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
912 Different prefixes of a candidate key column and a referencing foreign key column The naming must be consistent. Find foreign key constraints where the candidate key column and foreign key column names have different prefixes. Thus, for instance, one cannot use USING syntax for joining the tables. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
913 Different suffixes of a candidate key column and a referencing foreign key column This query identifies naming inconsistencies in foreign key relationships by comparing the suffixes of foreign key columns against their referenced candidate key columns. It flags pairs where the suffixes diverge (e.g., referencing user_id via a column named user_code or user_ref). Such discrepancies prevent the use of the simplified ANSI SQL USING syntax for joins, forcing the use of explicit ON clauses. The goal is to enforce a standardized suffix convention (typically _id) across the schema to improve maintainability and query readability. Problem detection system catalog base tables only 2025-12-14 11:43 MIT License View
914 Different tasks of rules Find different tasks that are solved by using rules, i.e., different rules on the same table or different tables that do the same thing are considered to solve one task. General system catalog base tables only 2025-11-07 10:11 MIT License View
915 Disabled rules Identify disabled rules. These should be enabled or dropped, otherwise these are dead code. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
916 Disabled system triggers (i.e., disabled enforcement of constraints) These triggers should be enabled because otherwise some important functionality regarding constraints like enforcing referential integrity does not work. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
917 Disabled user triggers Identify disabled triggers. These should be enabled or dropped, otherwise these are dead code. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
918 Do not always depend on one's parent Find where a hierarchical structure is implemented in a base table by adding a foreign key that refers to a candidate key of the same table. General system catalog base tables only 2025-11-07 10:11 MIT License View
919 Do not always depend on one's parent - column names are ot sufficiently different Find where a hierarchical structure is implemented in a base table by adding a foreign key that refers to a candidate key of the same table. Find only cases where the candidate key and foreign key column names are very similar (Levenshtein distance shorter than four). Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
920 Do not leave out the referential constraints (islands) Try to find missing foreign key constraints. Find base tables that do not participate in any referential constraint (as the referenced table or as the referencing table). These tables are like "islands" in the database schema. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View