PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 997 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
541 Perhaps excessive privileges to use views Find non-SELECT privileges to use views (for others than the owner of the view). Perhaps there should be only the privilege to make queries (SELECT statements based on the views) and data modification takes place by using routines. REFERENCES and TRIGGER privileges are definitely not needed. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
542 Database connect privilege is missing Find non-superusers who have a privilege to use a table or a routine but do not have the privilege to connect to the database. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-21 13:51 MIT License View
543 Base table column of national identification numbers does not have a correct data type Find non-textual base table columns that name refers to the possibility that these are used to register national identification numbers (personal codes). The codes can contain additional symbols to numbers. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View
544 Non-updatable views with DO INSTEAD NOTHING rules Find non-updatable views that have a DO INSTEAD NOTHING rule. The rule is used to prevent updates. However, the view is aniway non-updatable. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-10-28 13:01 MIT License View
545 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 2021-02-25 17:30 MIT License View
546 Perhaps unnecessay regular expression Find occurrences of possibly pointless regular expressions - i.e., value contains zero or more symbols. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-27 18:37 MIT License View
547 One-to-one relationships Find one-to-one relationships between base tables. In this case the foreign key columns must have primary key or unique constraint. These tables could implement inheritance hierarchy that has been specified in the conceptual data model. General system catalog base tables only 2023-01-06 13:39 MIT License View
548 Optional base table columns that have a default value that is not the empty string Find optiona base table columns that have a default value that is not the empty string. Such columns should be mandatory. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View
549 Optional base table columns Find optional base table columns, i.e., columns that permit NULLs. Are you sure you want to allow NULLs in these columns? General INFORMATION_SCHEMA only 2020-11-21 03:02 MIT License View
550 Perhaps default value 'infinity' is missing Find optional base table columns that have a timestamp type and do not have a default value. Problem detection INFORMATION_SCHEMA only 2024-11-28 14:58 MIT License View
551 Optional non-foreign key base table columns that participate in a UNIQUE constraint or index Find optional base table columns that participate in a UNIQUE constraint or index but do not participate in a foreign key constraint. Each base table has one or more candidate keys. One of these is usually selected to be the primary key, other are called alternate keys. To enforce an alternate key one should define a UNIQUE constraint and determine that all the key columns are mandatory (NOT NULL) just like the primary key columns are mandatory. Make sure that the NOT NULL constraint is not missing on these columns. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-10-21 10:45 MIT License View
552 Optional base table columns that participate in a UNIQUE constraint or index Find optional base table columns that participate in a UNIQUE constraint or index. Each base table has one or more candidate keys. One of these is usually selected to be the primary key, other are called alternate keys. To enforce an alternate key one should define a UNIQUE constraint and determine that all the key columns are mandatory (NOT NULL) just like the primary key columns are mandatory. Make sure that the NOT NULL constraint is not missing on these columns. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-10-21 15:57 MIT License View
553 Optional composite foreign keys that do not have MATCH FULL specified Find optional composite foreign keys that do not have MATCH FULL specified. Without MATCH FULL the system will permit partial foreign key values Problem detection system catalog base tables only 2021-02-25 17:29 MIT License View
554 Very similar (but not equal) routine names Find pairs of names of different types of routines that are very similar but not equal. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-11-30 12:09 MIT License View
555 Very similar table names Find pairs of names of different types of tables that are very similar or even equal. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-11-30 11:50 MIT License View
556 Very similar domain names Find pairs of names of domains that are very similar or even equal. Problem detection INFORMATION_SCHEMA only 2024-11-30 12:07 MIT License View
557 Duplicate non-fuction based unique indexes Find pairs of non-function based unique indexes that cover the same set of columns. Include indexes that support a constraint (primary key, unique, exclude), i.e., these indexes have been automatically created due to the constraint declaration. For instance, it helps us to find unique indexes that have been defined to already unique columns. Problem detection system catalog base tables only 2022-10-21 10:17 MIT License View
558 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 2022-10-21 15:56 MIT License View
559 All short cycles (tables) Find pairs of tables that have both a foreign key that references to the other table. Such cycles can involve more than two tables but the query detects only cycles with two tables. General INFORMATION_SCHEMA+system catalog base tables 2021-11-27 20:54 MIT License View
560 Short cycles (tables) Find pairs of tables that have both a mandatory (NOT NULL) and not defrerrable foreign key that references to the other table. Such cycles can involve more than two tables but the query detects only cycles with two tables. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-27 20:52 MIT License View