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
521 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
522 Perhaps unnecessary privileges to use the database Find as to whether a database user (except postgres), who is not a superuser, has Create (C) or Temporary (T) privileges to use the database. Problem detection system catalog base tables only 2021-11-23 20:42 MIT License View
523 All database privileges Find the privileges to use the database. c - CONNECT; C - CREATE; T - TEMPORARY. General system catalog base tables only 2021-11-23 20:28 MIT License View
524 PUBLIC has the USAGE privilege of a schema Find schemas where PUBLIC has the usage privilege. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-23 17:52 MIT License View
525 The number of default values Find the total number of columns with a default value as well as the number of columns with different kinds of default values (the number of columns where the default is used to implement surrogate key, the number of columns where the default is not used to implement surrogate key, the number of columns with a static default value, the number of columns with a dynamic default value). Sofware measure INFORMATION_SCHEMA only 2021-11-22 13:26 MIT License View
526 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
527 Missing USAGE privileges on schema If a user has a privilege to use a schema object, then the user must also have the usage privilege on the schema that contains the object. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-21 12:06 MIT License View
528 Naming of tables Find the names of different types (base table, foreign table, view, materialized view) of tables. Naming of tables must be consistent. For instance, do not mix names in plural and singular form within the same table type. General INFORMATION_SCHEMA+system catalog base tables 2021-11-17 20:13 MIT License View
529 Naming of declarative base table constraints Find the names of declarative base table constraints. Naming of constraints must be consistent. For instance, do not mix system-defined and user-defined names. General INFORMATION_SCHEMA+system catalog base tables 2021-11-17 20:11 MIT License View
530 All column dynamic DEFAULT values values that do not invoke a sequence Find all columns that have a dynamic default value, i.e., the value is returned by a function but the function is not for invoking a sequence. General INFORMATION_SCHEMA only 2021-11-15 15:57 MIT License View
531 All column static DEFAULT values Find all columns that have a static default value, i.e., the value is not returned by a function. General INFORMATION_SCHEMA only 2021-11-15 15:57 MIT License View
532 Tables with the same name in different schemas Find tables with the same name in different schemas. Make sure that this is not a duplication. General INFORMATION_SCHEMA+system catalog base tables 2021-11-10 15:40 MIT License View
533 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 2021-11-10 14:44 MIT License View
534 Too wide composite indexes Find composite indexes that do not support any constraint but are on more than three columns. Problem detection system catalog base tables only 2021-11-06 02:40 MIT License View
535 Coverage by routines that have the SQL-standard body Find for each base table the list of routines (functions and procedures) that refer to the base table. If the database is used through the public database interface (virtual data layer), then, ideally, each table is referred from at least one routine. General INFORMATION_SCHEMA+system catalog base tables 2021-11-05 17:00 MIT License View
536 User-defined routines that use keyword DECLARE but do not declare anything Find user-defined routines that use keyword DECLARE but do not declare anything. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-05 14:47 MIT License View
537 LEAKPROOF routines that are perhaps not leakproof You should not give wrong information to the database management system. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-05 14:12 MIT License View
538 FOR UPDATE is not needed if there is no FROM clause in the SELECT statement Find routines that use SELECT … FOR UPDATE without selecting rows from a specific table. For instance: SELECT 'text' AS v FOR UPDATE; Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-05 14:10 MIT License View
539 Incorrect characterization of a user-defined routine as an "immutable" routine Find immutable routines that contain SELECT statements from a database (including SELECT .. FOR UPDATE), INSERT, UPDATE, DELETE, or LOCK statement. The DBMS should get a correct background information about the behaviour of a routine. Immutable routines should not have side effects and should also not ask data from a database because it could change over time. Immutable routines cannot lock tables or its specific rows. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-05 14:08 MIT License View
540 Incorrect characterization of a user-defined routine as a "stable" routine Find stable routines that contain INSERT, UPDATE, DELETE, SELECT … FOR UPDATE, or LOCK statement. The DBMS should get a correct background information about the behaviour of a routine. Stable routines cannot modify data in a database, lock tables, or its specific rows. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-05 13:59 MIT License View