Filter Queries

Found 1077 queries.

Page 49 of 54
  • 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 the relatively small amount of data in the system catalog of a database.

#961. Foreign key refers to a table that has at least one subtable in the inheritance hierarchy

system catalog base tables only

Find foreign key constraints that refer to a base table that has at least one subtable in the inheritance hierarchy. Rows of the subtable do not belong to the supertable in terms of checking the referential integrity. Let us assume that there is a table T with a subtable Tsub. Let us also assume that table B has a foreign key that refers to the table T. If a row is inserted into Tsub, then this row cannot be referenced from B.

Problem detection License: MIT (opens in new tab)

#962. Foreign keys with ON DELETE CASCADE

system catalog base tables only

This query identifies all foreign key constraints that use ON DELETE CASCADE for the purpose of a design audit. The results must be manually reviewed to verify that each use case correctly implements a specific conceptual relationship. According to design principles, ON DELETE CASCADE is only appropriate for implementing generalization hierarchies (is-a relationships), compositions (strong ownership), or the existential dependency of a non-main entity on a main entity. Any usage outside of these patterns is considered a potential design flaw.

General License: MIT (opens in new tab)

#963. Foreign keys with ON UPDATE CASCADE

system catalog base tables only

This query generates a list of all foreign key constraints that are defined with the ON UPDATE CASCADE action. This list must be manually audited to enforce the design principle that this action should be applied exclusively to relationships involving mutable, natural keys. Any instance found referencing an immutable surrogate key should be considered a design flaw and remediated.

General License: MIT (opens in new tab)

#964. Function Upper or Lower is used in an index on a non-textual column

system catalog base tables only

Find function-based indexes that are based on function Upper or Lower but have been defined on a non-textual column. Such indexes support case insensitive search but in case of non-textual columns this does not have a meaning.

Problem detection License: MIT (opens in new tab)

#965. Grantable privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers

system catalog base tables only

Find privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible.

Problem detection License: MIT (opens in new tab)

#966. Grantable roles

system catalog base tables only

Find roles that a member can grant to others, i.e., the role has been granted with ADMIN OPTION. The number of privileges that can be passed on should be as small as possible.

Problem detection License: MIT (opens in new tab)

#967. Granted roles

system catalog base tables only

Find membership relations between roles.

General License: MIT (opens in new tab)

#968. Gratuitous context in the names of foreign key columns

system catalog base tables only

Find foreign key columns that name contains twice the name of the referenced (primary) table.

Problem detection License: MIT (opens in new tab)

#969. Gratuitous context in the names of schema objects

system catalog base tables only

This query identifies schema objects with names that are redundantly prefixed with their own schema's name. It flags any object whose name begins with the schema name plus at least one other character. This enforces the design principle that a schema is a sufficient namespace, and therefore, objects within it do not require the additional, repetitive context in their own names.

Problem detection License: MIT (opens in new tab)

#970. Identical indexes

system catalog base tables only

Find indexes that are identical, i.e., have the same properties, including uniqueness. The query considers all types of indexes, including indexes that have been automatically created to support a constraint and function-based indexes.

Problem detection License: MIT (opens in new tab)

#971. Inconsistency (code vs. id) of naming foreign key and referenced candidate key columns

system catalog base tables only

Naming of foreign key and referenced candidate key columns should be consistent. It cannot be so that in one table a value is labeled "id" like some surrogate key value and in another it "turns" into human-usable "code" or vice versa. An example:

Person(person_id, name)
Primary Key (person_id)

E_mail_address(e_mail_address_id, person_code, address)
Primary Key (e_mail_address_id)
Foreign key (person_code) References Person (person_id)

Problem detection License: MIT (opens in new tab)

#972. Inconsistent chain of relationships in terms of using ON UPDATE compensating action

system catalog base tables only

In case of a chain of relationships between tables (where the primary key and the foreign key have the same columns) the use of ON UPDATE compensating action should be consistent. For instance, in the next example there is inconsistency, because if one changes the person_code in table Person, then the modification does not succeed because it does not cascade to the table Product. It is unclear as to whether it should be possible to change the person_code or not. Person (person_code, surname) Primary key (person_code) Worker(person_code)
Primary key (person_code)
Foreign key (person_code) References Person (person_code) ON UPDATE CASCADE

Product(product_code, registrator)
Primary key (product_code)
Foreign key (registrator) References Worker (person_code) ON UPDATE NO ACTION

Problem detection License: MIT (opens in new tab)

#973. Inconsistent two-column CHECK constraints

system catalog base tables only

This query identifies inconsistencies in two-column CHECK constraints across the database. Specifically, it flags cases where different tables share the exact same pair of columns, but the logical expressions governing them differ. For example, one table might enforce last_change_time >= reg_time, while another strictly enforces last_change_time > reg_time. Highlighting these discrepancies helps ensure uniform data validation rules.

Problem detection License: MIT (opens in new tab)

#974. Index FILLFACTOR is not default

system catalog base tables only

This query generates a list of all indexes with an explicitly configured, non-default FILLFACTOR for the purpose of a performance audit. The query is aware of the different default FILLFACTOR values associated with various index access methods (e.g., 90 for B-tree, 100 for others like GiST/GIN). This allows administrators to quickly identify and review all instances of customized index storage parameters to assess if these non-standard configurations are justified and still effective.

General License: MIT (opens in new tab)

#975. Index names missing table identifiers or abbreviations

system catalog base tables only

This query identifies standalone indexes—specifically those not backing a declarative constraint (such as a PRIMARY KEY or UNIQUE constraint)—that fail to follow standard naming conventions. It flags indexes that omit both the parent table's full name and its acronym (an abbreviation formed from the initial letters of its name components). Incorporating the table identifier into the index name is crucial: in PostgreSQL, indexes share the schema-wide namespace with tables, meaning this naming practice prevents collisions and provides immediate clarity when inspecting execution plans, performance statistics, or slow query logs.

Problem detection License: MIT (opens in new tab)

#976. Installed extensions

system catalog base tables only

Try to use as much the possibilities of the DBMS as possible. On the other hand, do not install extensions that are not needed in order not to overcomplicate the database.

General License: MIT (opens in new tab)

#977. Many-to-many tables that need conceptual renaming

system catalog base tables only

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 License: MIT (opens in new tab)

#978. Many-to-many tables that perhaps need conceptual renaming

system catalog base tables only

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 License: MIT (opens in new tab)

#979. More than one index on a column

system catalog base tables only

Find base table columns that belong to more than one index (including automatically created indexes that support constraints).

General License: MIT (opens in new tab)

#980. Multiple constraints with the same name

system catalog base tables only

This query identifies table constraints (CHECK, FOREIGN KEY, PRIMARY KEY, UNIQUE, and EXCLUDE) that share the exact same name across multiple base or foreign tables. According to the SQL standard, constraint names must be unique within a schema. However, PostgreSQL only enforces constraint name uniqueness at the table level for CHECK and FOREIGN KEY constraints (and at the schema level for index-backed constraints). Allowing multiple tables to use identical constraint names causes several issues:


  • Broken INFORMATION_SCHEMA queries: Standard INFORMATION_SCHEMA views (such as check_constraints, referential_constraints, and constraint_column_usage) assume that the pair (constraint_schema, constraint_name) uniquely identifies a constraint. Duplicate names within a schema cause joins across these views to produce Cartesian products and inaccurate metadata results.
  • Ambiguous error handling and logging: When a constraint violation occurs, application error handlers, ORMs, and database administrators relying on the constraint name cannot immediately determine which table caused the failure without inspecting additional context.

Constraints that are inherited by or redefined on child tables (INHERITS or declarative partitions) with the same name as on their parent table are excluded, as PostgreSQL table inheritance naturally propagates parent constraint names to child relations.

Problem detection License: MIT (opens in new tab)
# Name(activate to sort ascending) Goal(activate to sort ascending) Type(activate to sort ascending) Data source(sorted ascending, activate to sort descending) Last update(activate to sort ascending) License Actions
961 Foreign key refers to a table that has at least one subtable in the inheritance hierarchy Find foreign key constraints that refer to a base table that has at least one subtable in the inheritance hierarchy. Rows of the subtable do not belong to the supertable in terms of checking the referential integrity. Let us assume that there is a table T with a subtable Tsub. Let us also assume that table B has a foreign key that refers to the table T. If a row is inserted into Tsub, then this row cannot be referenced from B. Problem detection system catalog base tables only MIT (opens in new tab) View
962 Foreign keys with ON DELETE CASCADE This query identifies all foreign key constraints that use ON DELETE CASCADE for the purpose of a design audit. The results must be manually reviewed to verify that each use case correctly implements a specific conceptual relationship. According to design principles, ON DELETE CASCADE is only appropriate for implementing generalization hierarchies (is-a relationships), compositions (strong ownership), or the existential dependency of a non-main entity on a main entity. Any usage outside of these patterns is considered a potential design flaw. General system catalog base tables only MIT (opens in new tab) View
963 Foreign keys with ON UPDATE CASCADE This query generates a list of all foreign key constraints that are defined with the ON UPDATE CASCADE action. This list must be manually audited to enforce the design principle that this action should be applied exclusively to relationships involving mutable, natural keys. Any instance found referencing an immutable surrogate key should be considered a design flaw and remediated. General system catalog base tables only MIT (opens in new tab) View
964 Function Upper or Lower is used in an index on a non-textual column Find function-based indexes that are based on function Upper or Lower but have been defined on a non-textual column. Such indexes support case insensitive search but in case of non-textual columns this does not have a meaning. Problem detection system catalog base tables only MIT (opens in new tab) View
965 Grantable privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers Find privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible. Problem detection system catalog base tables only MIT (opens in new tab) View
966 Grantable roles Find roles that a member can grant to others, i.e., the role has been granted with ADMIN OPTION. The number of privileges that can be passed on should be as small as possible. Problem detection system catalog base tables only MIT (opens in new tab) View
967 Granted roles Find membership relations between roles. General system catalog base tables only MIT (opens in new tab) View
968 Gratuitous context in the names of foreign key columns Find foreign key columns that name contains twice the name of the referenced (primary) table. Problem detection system catalog base tables only MIT (opens in new tab) View
969 Gratuitous context in the names of schema objects This query identifies schema objects with names that are redundantly prefixed with their own schema's name. It flags any object whose name begins with the schema name plus at least one other character. This enforces the design principle that a schema is a sufficient namespace, and therefore, objects within it do not require the additional, repetitive context in their own names. Problem detection system catalog base tables only MIT (opens in new tab) View
970 Identical indexes Find indexes that are identical, i.e., have the same properties, including uniqueness. The query considers all types of indexes, including indexes that have been automatically created to support a constraint and function-based indexes. Problem detection system catalog base tables only MIT (opens in new tab) View
971 Inconsistency (code vs. id) of naming foreign key and referenced candidate key columns Naming of foreign key and referenced candidate key columns should be consistent. It cannot be so that in one table a value is labeled "id" like some surrogate key value and in another it "turns" into human-usable "code" or vice versa. An example:

Person(person_id, name)
Primary Key (person_id)

E_mail_address(e_mail_address_id, person_code, address)
Primary Key (e_mail_address_id)
Foreign key (person_code) References Person (person_id)
Problem detection system catalog base tables only MIT (opens in new tab) View
972 Inconsistent chain of relationships in terms of using ON UPDATE compensating action In case of a chain of relationships between tables (where the primary key and the foreign key have the same columns) the use of ON UPDATE compensating action should be consistent. For instance, in the next example there is inconsistency, because if one changes the person_code in table Person, then the modification does not succeed because it does not cascade to the table Product. It is unclear as to whether it should be possible to change the person_code or not. Person (person_code, surname) Primary key (person_code) Worker(person_code)
Primary key (person_code)
Foreign key (person_code) References Person (person_code) ON UPDATE CASCADE

Product(product_code, registrator)
Primary key (product_code)
Foreign key (registrator) References Worker (person_code) ON UPDATE NO ACTION
Problem detection system catalog base tables only MIT (opens in new tab) View
973 Inconsistent two-column CHECK constraints This query identifies inconsistencies in two-column CHECK constraints across the database. Specifically, it flags cases where different tables share the exact same pair of columns, but the logical expressions governing them differ. For example, one table might enforce last_change_time >= reg_time, while another strictly enforces last_change_time > reg_time. Highlighting these discrepancies helps ensure uniform data validation rules. Problem detection system catalog base tables only MIT (opens in new tab) View
974 Index FILLFACTOR is not default This query generates a list of all indexes with an explicitly configured, non-default FILLFACTOR for the purpose of a performance audit. The query is aware of the different default FILLFACTOR values associated with various index access methods (e.g., 90 for B-tree, 100 for others like GiST/GIN). This allows administrators to quickly identify and review all instances of customized index storage parameters to assess if these non-standard configurations are justified and still effective. General system catalog base tables only MIT (opens in new tab) View
975 Index names missing table identifiers or abbreviations This query identifies standalone indexes—specifically those not backing a declarative constraint (such as a PRIMARY KEY or UNIQUE constraint)—that fail to follow standard naming conventions. It flags indexes that omit both the parent table's full name and its acronym (an abbreviation formed from the initial letters of its name components). Incorporating the table identifier into the index name is crucial: in PostgreSQL, indexes share the schema-wide namespace with tables, meaning this naming practice prevents collisions and provides immediate clarity when inspecting execution plans, performance statistics, or slow query logs. Problem detection system catalog base tables only MIT (opens in new tab) View
976 Installed extensions Try to use as much the possibilities of the DBMS as possible. On the other hand, do not install extensions that are not needed in order not to overcomplicate the database. General system catalog base tables only MIT (opens in new tab) View
977 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 MIT (opens in new tab) View
978 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 MIT (opens in new tab) View
979 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 MIT (opens in new tab) View
980 Multiple constraints with the same name This query identifies table constraints (CHECK, FOREIGN KEY, PRIMARY KEY, UNIQUE, and EXCLUDE) that share the exact same name across multiple base or foreign tables. According to the SQL standard, constraint names must be unique within a schema. However, PostgreSQL only enforces constraint name uniqueness at the table level for CHECK and FOREIGN KEY constraints (and at the schema level for index-backed constraints). Allowing multiple tables to use identical constraint names causes several issues:

  • Broken INFORMATION_SCHEMA queries: Standard INFORMATION_SCHEMA views (such as check_constraints, referential_constraints, and constraint_column_usage) assume that the pair (constraint_schema, constraint_name) uniquely identifies a constraint. Duplicate names within a schema cause joins across these views to produce Cartesian products and inaccurate metadata results.
  • Ambiguous error handling and logging: When a constraint violation occurs, application error handlers, ORMs, and database administrators relying on the constraint name cannot immediately determine which table caused the failure without inspecting additional context.

Constraints that are inherited by or redefined on child tables (INHERITS or declarative partitions) with the same name as on their parent table are excluded, as PostgreSQL table inheritance naturally propagates parent constraint names to child relations.
Problem detection system catalog base tables only MIT (opens in new tab) View