PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1053 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
981 Overlapping non-function based indexes that have the same leading column but with different operator class Find non-function based indexes (both unique and non-unique) that have identical first column but the operator class that is used in case of the first column is different. Include unique indexes that support a constraint (primary key, unique, exclude), i.e., these indexes have been automatically created due to the constraint declaration. General system catalog base tables only 2025-11-07 10:11 MIT License View
982 Overlapping non-function based indexes that have the same leading column with the same operator class Find non-function based indexes (both unique and non-unique) that duplicate each other because their first column is identical and the operator class that is used in case of the first column is identical. Include unique indexes that support a constraint (primary key, unique, exclude), i.e., these indexes have been automatically created due to the constraint declaration. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
983 Patterns of the names of columns of simple primary keys Find the patterns of the names of columns of simple primary keys. Make sure that the naming is consistent. Ideally, the names should indicate as to whether the column is a surrogate or a natural key column. PostgreSQL 14 added primary keys, unique constraints, and foreign keys to system catalogs. Thus the query was modified to exclude the results from the system catalog. General system catalog base tables only 2025-11-07 10:11 MIT License View
984 Percentage of the total index storage size from the total database storage size (system catalog included) Get overview of disk usage. Sofware measure system catalog base tables only 2025-11-07 10:11 MIT License View
985 Perhaps an inconsistent use of NO ACTION and RESTRICT in the foreign key declarations Find as to whether in case of foreign key constraints both the compensating actions RESTRICT and NO ACTION are used within the same database. If the same thing has to do in different places, then try to do it in the same way. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
986 Perhaps a relationship should be irreflexive Enforce all the constraints. A binary relation is called irreflexive, if it does not relate any element to itself. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
987 Perhaps a too generic foreign key column name Find the names of foreign key columns that are too generic. The expressive names of table columns allow database users better and more quickly understand the meaning of data in the database. A person could participate in a process or be associated with an object due to different reasons. Thus, foreign key column names like isik_id, person_id, tootaja_id, worker_id etc. are too generic. The name should refer (also) to the reason why the person is connected. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
988 Perhaps primary key columns could be renamed Find the names of simple primary key columns that name does not follow the pattern _id or _code but it is quite similar. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
989 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 2025-11-07 10:11 MIT License View
990 Perhaps un-trimmed string concatenation in derived tables This query identifies derived tables (views and materialized views) containing string concatenation logic that introduces potential leading or trailing whitespace. It targets expressions using the concatenation operator (||), concat(), or format() that may inject separators (such as spaces) but lack a surrounding trim() function. This pattern often results in "dangling separators" when one of the concatenated components is null or empty, degrading data quality and presentation. Problem detection system catalog base tables only 2025-12-22 18:28 MIT License View
991 Personal names are unique Find all primary key and unique constraints of base tables that involve at least one column with personal names. Personal names are not unique and these cannot be (usually) used to guarantee uniqueness. Either one has declared a superkey instead of candidate key (i.e., the key contains a column that is not needed for uniqueness) or the key columns have been found incorrectly. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
992 Privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers This query performs a comprehensive security audit of access control lists (ACLs) across a wide range of database objects, including the database itself, schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers. It retrieves privileges granted to any principal other than the object's owner or the systemic postgres superuser. The objective is to identify and validate "third-party" access rights, ensuring compliance with the principle of least privilege and preventing unauthorized access accumulation. General system catalog base tables only 2026-01-21 10:52 MIT License View
993 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 2025-11-07 10:11 MIT License View
994 Publications with no tables Find publications that do not contain any table. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
995 PUBLIC has TEMPORARY privilege in the database Find as to whether PUBLIC (all current and future users) has TEMPORARY privilege in the database. PUBLIC gets the privilege by default. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
996 Recursive relationships with the same source and target Find incorrectly implemented adjacency lists. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
997 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 2025-11-07 10:11 MIT License View
998 Redundant indexes Find indexes that may be redundant. In addition to identical indexes it also considers indexes that cover the same columns and have the same properties except 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 2025-11-07 10:11 MIT License View
999 Referential degree of tables (ver 2) Find how many base tables are referenced from a base table by using foreign keys. Sofware measure system catalog base tables only 2025-11-07 10:11 MIT License View
1000 Rules with the same name in different schemas Find rule names that are used in a database in more than one schema. Different things should have different names. But here different rules have the same name. Also make sure that this is not a duplication. General system catalog base tables only 2025-11-07 10:11 MIT License View