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
461 All enumerated types Find all enumerated types. General INFORMATION_SCHEMA+system catalog base tables 2022-10-31 10:19 MIT License View
462 CHAR or VARCHAR columns have a default value that length is longer from the character maximum length of the column Find table columns with CHAR or VARCHAR type that have a default value that length is longer from the character maximum length of the column. Choose a suitable data type, field size, and default value. If the value is longer, then it is impossible to register it in a field, i.e., it makes registration of data impossible (except if the excessive characters are spaces). Problem detection INFORMATION_SCHEMA only 2022-10-31 10:19 MIT License View
463 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 2022-10-31 10:19 MIT License View
464 All column DEFAULT values Find all the default values of base table, view, and foreign table columns. General INFORMATION_SCHEMA only 2022-10-31 10:18 MIT License View
465 All clustered indexes Find all tables that rows have been physically sorted based on an index. General system catalog base tables only 2022-10-31 10:17 MIT License View
466 Base table column of personal names has questionable properties Find base table columns that name refers to the possibility that these are used to register personal names. Although there are very long personal names the general approach is to register a shortened version of these. Thus, a large field size is not a good idea because it would cause usability and security problems. There are persons who only have one name (mononymous persons). Database design must take it into account. Problem detection INFORMATION_SCHEMA only 2022-10-29 20:35 MIT License View
467 Chains of ON DELETE CASCADE Find all referential paths (chains of of parent-child tables that are associated through foreign key constraints) where all foreign key constraints have ON DELETE CASCADE compensating actions. Be careful with too long chains. General system catalog base tables only 2022-10-29 20:04 MIT License View
468 Number of rows in base tables Find the number of rows in base tables. General system catalog base tables only 2022-10-27 19:44 MIT License View
469 System-generated domain CHECK constraint names Find the names of domain CHECK constraints that have been system-generated. 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. Problem detection INFORMATION_SCHEMA only 2022-10-27 15:44 MIT License View
470 Logical size of a schema The schema size is the sum of the sizes of all tables in the schema. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2022-10-21 16:18 MIT License View
471 Logical size of a table The table size is the sum of the total size of the simple columns and the total size of the complex columns in the table. In case of SQL databases large base tables in terms of number of columns could be a side effect of the problems with cloned columns or multiple columns for the same attribute. A base table with a low normalization level, which is meant to hold data that corresponds to multiple entity types has typically also relatively large number of columns compared with other base tables. Thus, the normalization level of base tables with a large number of columns should be checked as well. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2022-10-21 16:17 MIT License View
472 Recursive rules that directly modify their home table Do not cause potentially infinite loops. Recursive rules would fire itself over and over again. Although the system is able to detect these after executing a data modification statement it is better to avoid creating these altogether. Problem detection system catalog base tables only 2022-10-21 15:59 MIT License View
473 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
474 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
475 Empty tables Find base tables where the number of rows is zero. If there are no rows in a table, then it may mean that one hasn't tested constraints that have been declared to the table or implemented by using triggers. It could also mean that the table is not needed because there is no data that should be registered in the table. Problem detection system catalog base tables only 2022-10-21 15:55 MIT License View
476 Privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that have been granted to a superuser Find privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that have been granted to a superuser. Superuser can do anything in the database and thus does not need the privileges. The result is a sign that perhaps the executed GRANT statements were incorrect (wrong username) or the grantee later got superuser status (that it shouldn't have). Problem detection system catalog base tables only 2022-10-21 15:53 MIT License View
477 The number of user-defined rules by schema and in total Find the number of rules in a database in different schemas, excluding the rules that are created to support views. Rules can be used to maintain data integrity in a database by causing rejection of incorrect insertions and updates. Therefore, the number of rules in a database gives an indication about the state of enforcing constraints at the database level. Sofware measure system catalog base tables only 2022-10-21 11:29 MIT License View
478 Base tables with plenty of data Find base tables that have 1000 rows or more. General system catalog base tables only 2022-10-21 11:25 MIT License View
479 Disabled rules Identify disabled rules. These should be enabled or dropped, otherwise these are dead code. Problem detection system catalog base tables only 2022-10-21 11:22 MIT License View
480 The number of user-defined rules Find the number of rules in a database, excluding the rules that are associated with system catalog tables and rules that are created to support views. Rules can be used to maintain data integrity in a database by causing rejection of incorrect insertions and updates. Therefore, the number of rules in a database gives some indications about the state of enforcing constraints at the database level. Sofware measure system catalog base tables only 2022-10-21 11:19 MIT License View