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
761 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
762 Perhaps a routine does not have a real task Find the routines where the only action is to return an argument value, a constant value, NULL or return the value of OLD or NEW variable in case of trigger functions. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-05 12:09 MIT License View
763 The size of base tables and their indexes Find the size of base tables without indexes, size of the indexes of the table, total size of the table (including its indexes) and percentage of the index size from the total size. If the size of indexes of a table is relatively high, then check as to whether all the indexes are needed. General system catalog base tables only 2023-10-27 20:38 MIT License View
764 The generic names (columns) (aggregate view) Find the too generic column names and the number of their occurrences. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-05-03 13:53 MIT License View
765 The longest names of database objects Find the TOP 3 longest (identifiers) names of user-defined objects. General INFORMATION_SCHEMA+system catalog base tables 2024-12-21 17:26 MIT License View
766 The number of columns based on table type Find the total number of columns in the different types of tables as well as average number of columns in the tables as well as minimal and maximal number of columns. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2022-11-10 14:12 MIT License View
767 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
768 Incorrect check of NULLs Find the use of =NULL and <>NULL in case of table level check constraints, domain level check constraints, WHEN clauses of triggers, WHERE clauses of rules, subqueries of derived tables, and bodies of routines. Write correct code. In order to determine as to whether a value is missing or not one has to use the IS [NOT] NULL predicate. NULL is the marker in SQL that denotes a missing value. Although it is often called "NULL value", one cannot treat it as an ordinary value, i.e., use it in comparisons as a value. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-12 12:10 MIT License View
769 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
770 The same trigger function is used in case of multiple tables Find trigger functions that are used in case of more than one table. Although it is legal, one must be careful when changing the functions in order to avoid unwanted consequences. General INFORMATION_SCHEMA only 2020-12-25 14:50 MIT License View
771 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
772 Triggers with the same name in different schemas Find trigger names that are used in a database in more than one schema. Different things should have different names. But here different triggers have the same name. Also make sure that this is not a duplication. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
773 Triggers that are used to calculate tsvector values react to a wrong set of events Find triggers on base tables that are used to calculate tsvector values that react to a wrong set of events, i.e., react to the DELETE event or do not react to the INSERT and UPDATE events. Problem detection system catalog base tables only 2023-11-07 10:14 MIT License View
774 Triggers with arguments from the CREATE TRIGGER statement Find triggers that get an argument from the CREATE TRIGGER statement. General INFORMATION_SCHEMA+system catalog base tables 2023-12-22 12:35 MIT License View
775 Update prevention may prevent legal updates Find triggers that try prevent updating data in a certain column but prevent also certain legal updates - updates that write to a field a value that was in the field before the update. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-07-07 13:12 MIT License View
776 Too generic names (unique indexes) Find unique indexes that have too generic names like "key" or the name contain too generic words like "data" (all constraints restrict data in the table), or the name is an abbreviation of a constraint type name. Problem detection system catalog base tables only 2023-01-08 10:38 MIT License View
777 Too generic names (unique index columns) Find unique index (not associated with a constraint) columns with the names like id, identifikaator, code, kood, number, etc. The names could have underscores as the prefix or suffix. These are too generic names. Problem detection system catalog base tables only 2023-01-07 20:34 MIT License View
778 Unlogged tables Find unlogged tables. These may improve the performance of INSERT operations, but with the price of possibly loosing data - an unlogged table is automatically truncated after a crash or unclean shutdown. General system catalog base tables only 2020-11-06 14:51 MIT License View
779 Unnamed parameters Find unnamed parameters in PL/PGSQL routines that do not declare aliases for parameters and in SQL routines. Avoid unnamed parameters because dependency on position in case of referencing the parameters makes evolving the code more difficult. In case of unnamed parameters - if one changes the order of parameters in the routine signature, then one has to change the body of the routine in order to use correct references. The bigger is the number of parameters in a routine the more the unnamed parameters make it more difficult to understand the routine. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-03-15 18:16 MIT License View
780 Updatable views that do not have WITH CHECK OPTION constraint Find updatable views that do not have WITH CHECK OPTION constraint. WITH CHECK OPTION constraint prevents updates through the view that violate the predicate of the view. Such updates must be prevented. Problem detection INFORMATION_SCHEMA only 2023-10-29 10:33 MIT License View