PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1040 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
941 Many-to-many tables that need conceptual renaming This query identifies junction tables (implementing a binary relationship via two foreign keys) whose names are simple concatenations of the parent table names (e.g., Course_Lecturer). This naming style is flagged as a design smell because it merely describes the physical implementation rather than the conceptual relationship being modeled. The recommended best practice is to rename such tables to reflect the domain concept they represent. For instance, the relationship between Course and Lecturer should be named after the activity it represents, such as Teaching or Course_Assignment. Problem detection system catalog base tables only 2025-11-15 09:45 MIT License View
942 Many-to-many tables that perhaps need conceptual renaming This query identifies junction tables (implementing a binary relationship via two foreign keys) whose names are simple concatenations of the parent table names, often with minor variations (e.g., Courses_Lecturer). This naming convention is a design smell as it describes the physical implementation rather than the conceptual relationship being modeled. The recommended best practice is to rename such tables to reflect the domain concept they represent. For instance, the relationship between Course and Lecturer should be named after the activity it represents, such as Teaching or Course_Assignment. Problem detection system catalog base tables only 2025-11-15 09:46 MIT License View
943 More than one index on a column Find base table columns that belong to more than one index (including automatically created indexes that support constraints). General system catalog base tables only 2025-11-07 10:11 MIT License View
944 Multicolumn CHECK constraints with with inconsistent Boolean expressions Find CHECK constraints that involve two columns, i.e., the cardinality of the constraint is 2, the columns have the same name in different tables, and the Boolean expressions of these constraints are different. For instance, in one table it is last_change_time>=reg_time and in another table it is not (reg_time>last_change_time). Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
945 Multiple inheritance Find instances of multiple inheriance of base tables. Make sure that multiple inheritance is indeed needed. General system catalog base tables only 2025-11-07 10:11 MIT License View
946 Multiple triggers that update tsvector values Find base tables that have multiple triggers to update tsvector values. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
947 Names of columns that hold personal names but do not take into account cultural diversity Find columns of tables (base tables, views, materialized views, foreign tables) that have the name first_name or last_name. Such column names do not take into account that different cultures use different personal name components and the number of possible components is more than two. If in a culture, the surname is presented before the given name, then the column names causes confusion. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
948 Names of constraints (directly connected to a base table) and non-unique indexes that do not contain the associated column name Find constraints that are perhaps badly named. Find names of constraints (directly connected to a base table) and non-unique indexes that do not contain the associated column name. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
949 Names of constraints (directly connected to a base table) that do not contain the table name Find constraints that are perhaps badly named. Table names help us to ensure the uniqueness of the names within a schema and make the names more expressive and user-friendly. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
950 Names of indexes that do not contain the table name Find indexes that do not support a declarative constraint and that are perhaps badly named. Table names make the names more expressive and user-friendly. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
951 Not enforced constraints This query identifies constraints (CHECK and FOREIGN KEY) that exist in the system catalog but are not actively enforced against the table data. Problem detection system catalog base tables only 2025-11-15 10:10 MIT License View
952 Not inherited CHECK constraints Find CHECK constraints that have been defined in a supertable (parent table) but not in its subtables. An entity that belongs to a subtype should also belong to its supertype. If a subtype entity satisfies some constraint, then logically it must also satisfy the constraints of the supertype as well. If CHECK constraints are not inherited, then this is not guaranteed. If you implement subtyping not merely reuse implementation in the subtables, then the subtables must have at least the same CHECK constraints as the supertable. CHECK(false) on a supertable is an appropriate CHECK constraint if one wants to prevent registering data directly to the supertable, i.e., data can only be added to the subtables. General system catalog base tables only 2025-11-07 10:11 MIT License View
953 Not inherited CHECK constraints that are recreated in the immediate subtable Find base table CHECK constraints that have been defined as NOT INHERITED but the constraint with the same Boolean expression has been defined in the immediate subtable of the table. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
954 Not inherited CHECK constraints that cover at least one column Find CHECK constraints that cover at least one column and that have been defined in a supertable (parent table) but not in its subtables. An entity that belongs to a subtype should also belong to its supertype. If a subtype entity satisfies some constraint, then logically it must also satisfy the constraints of the supertype as well. If CHECK constraints are not inherited, then this is not guaranteed. If you implement subtyping not merely reuse implementation in the subtables, then the subtables must have at least the same CHECK constraints as the supertable. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
955 NOT VALID foreign key constraints Find not valid foreign key constraints. These constraints have been created so that the existing data has not been checked against the constraint. It could be deliberate in case of legacy systems that have data quality problems. However, ideally all the data in the table conforms to the constraint. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
956 Number of rows in base tables Find the number of rows in base tables. General system catalog base tables only 2025-11-07 10:11 MIT License View
957 Number of system-generated and user-defined constraint names by constraint type (constraints that involve more than one column) Find the number of system-generated constraint names by constraint type. Names should follow the same style. If there is a mix of system-generated and user-defined names, then the style is most probably different. Sofware measure system catalog base tables only 2025-11-07 10:11 MIT License View
958 Number of system-generated and user-defined constraint names by constraint type (constraints that involve one column) Find the number of system-generated constraint names by constraint type. Names should follow the same style. If there is a mix of system-generated and user-defined names, then the style is most probably different. Sofware measure system catalog base tables only 2025-11-07 10:11 MIT License View
959 ON DELETE CASCADE is probably missing (based on the multiplicity of the relationship) This query identifies foreign key constraints that likely require an ON DELETE CASCADE action. It targets a specific pattern: where the foreign key constraint's columns are identical to a candidate key (Primary Key or Unique constraint) of the same table. This structure implements a one-to-one identifying relationship, where the child entity is existentially dependent on the parent (a weak entity depending on a strong entity). The absence of ON DELETE CASCADE in this scenario is a design flaw, as it prevents the parent row from being deleted and breaks the conceptual model. Problem detection system catalog base tables only 2025-11-08 10:53 MIT License View
960 ON DELETE CASCADE is probably not needed (based on the relationship type) This query identifies foreign key constraints that use ON DELETE CASCADE in a non-identifying relationship. A relationship is considered non-identifying if the foreign key columns in the child table are not part of the child table's candidate key. In such cases, the child entity has its own independent identity, and using ON DELETE CASCADE is often a design flaw that can lead to unexpected and catastrophic data loss. Problem detection system catalog base tables only 2025-11-07 20:02 MIT License View