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
261 Publicly accessible system catalog tables Find privileges to use system catalog base tables or views that have been granted to public. General INFORMATION_SCHEMA only 2023-11-24 14:56 MIT License View
262 Derived table uses a function to get data from another table Find views that use a function to get data from another table. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-24 14:36 MIT License View
263 Invocation of PL/pgSQL functions from the subqueries of derived tables Find derived tables that subqueries invoke a PL/pgSQL function. Avoid context switch that is caused by the invocation of PL/pgSQL functions from the subqueries of derived tables. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-24 14:30 MIT License View
264 Incorrect specification of logical or in regular expressions Find the use of regular expressions where logical or is incorrectly specified, i.e., (| or |). Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-24 12:07 MIT License View
265 A predefine character class has been incorrectly specified Find regular expressions where a predefined character class is incorrectly specified, e.g. [digit] instead of [:digit:]. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-23 12:09 MIT License View
266 Predefined character classes must be between double square brackets Find regular expressions that do not have predefined character classes between double square brackets, e.g., [:digit:] instead of [[:digit:]]. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-22 17:34 MIT License View
267 Trigger functions with a conditional statement Find trigger functions that contain a conditional (IF or CASE) but do not contain a SELECT statement before these. The latter condition is for the reason that one cannot use a subquery in the WHEN clause. Thus, if one wants to make a query and decide the further action based on the results of the query, then one must do it within the body of the function. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-19 12:07 MIT License View
268 Inappropriate field size or data type for column that strores database username Find columns of base tables that based on the default value of the column contain database username. However, the type of the column is not VARCHAR(63) or VARCHAR(128). Problem detection INFORMATION_SCHEMA only 2023-11-19 11:58 MIT License View
269 Perhaps spaces are unnecessarily restricted Find base table columns that name refers to the possibility that the column is used to record names or textual descriptions but the column seems to have a simple check constraint that restricts spaces in these. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-18 13:30 MIT License View
270 Double checking of the maximum character length Do not duplicate code. In this case a CHECK constraint duplicates the restriction that is already enforced with the help of the declaration of the maximum field size (for instance, VARCHAR(100)). Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-18 13:27 MIT License View
271 All security policies Find all security policies. General system catalog base tables only 2023-11-17 18:41 MIT License View
272 Views with unnecessary security invoker Find views with security invoker option that do not have any underlying base table with a security policy. Security invoker option of views is possible starting from PostgreSQL 15. "The main use case (and the one that inspired the feature) is to be able to use views and still check row-level security policies on the underlying tables as the invoker." Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-17 18:39 MIT License View
273 Optional foreign key columns Find foreign key columns that do not have the NOT NULL constraint. It is better to limit the use of NULLs and optional columns due to the problems that it causes in interpreting the query results, making queries, and enforcing constraints. In addition, one should check as to whether this kind of design is consistent with the multiplicities in the conceptual data model. General INFORMATION_SCHEMA only 2023-11-17 18:36 MIT License View
274 Potentially missing PRIMARY KEY or UNIQUE constraints (based on column names) Find columns of base tables that name refers to the possibility that it contains unique values but the column does not belong to any PRIMARY KEY/UNIQUE constraint. If something has to be unique, then it must be said to the system so that it could use the information for internal optimizations and enforce the constraint. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-17 18:09 MIT License View
275 Surrogate key columns that do not follow the naming style Find surrogate key columns that name does not end with "id_" or start with "id_". Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-16 12:19 MIT License View
276 ON UPDATE CASCADE is probably missing (based on column names) Find foreign key constraints that do not feature ON UPDATE CASCADE compensating action although people have a reason to change the key value in the primary table by assuming that the names of foreign key columns correctly point towards the use of natural keys in the table. Problem detection system catalog base tables only 2023-11-16 11:59 MIT License View
277 Potentially missing default values of base table columns Find columns of base tables without a default value that are either Boolean columns that based on the name seem to implement a state machine or temporal columns that based on the name seem to keep registration or update time. These columns often have a default value. Problem detection INFORMATION_SCHEMA only 2023-11-15 17:03 MIT License View
278 Perhaps too many square brackets Character classes are surrounded by two pairs of square brackets. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-15 10:47 MIT License View
279 Non-predefined character classes must not be between double square brackets Write correct regular expressions. For instance, if there is a rule that code must consist of one or more digits, then correct expression is code~'^[0-9]+$', not code~'^[[0-9]]+$'. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-13 12:32 MIT License View
280 Views with security invoker Find views that have the security invoker option. Such option is possible starting from PostgreSQL 15. In case of using such views one cannot create a system where data is accessed through views and the users (applications) do not have direct access to the base tables. Problem detection system catalog base tables only 2023-11-13 12:20 MIT License View