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
161 Only one value permitted in a base table or a foreign table column (based on check constraints) Find columns of base tables or foreign tables in case of which a check constraint on the column permits only one value in the column. The constraint may be correct if it is applied to a column of a subtable that is inherited from the supertable or is used to enfore the rule that the table can have at most one row. General INFORMATION_SCHEMA+system catalog base tables 2024-04-30 20:14 MIT License View
162 YELLING IN COMMENTS! Find comments of derived tables and routines that consist of only uppercase letters. Do not use only uppercase (capital) letters in order to write comments. It means yelling and also makes text less readable. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-04-25 15:13 MIT License View
163 The SQL-language routines with the body that is string literal Find SQL-language routines that have the body that is string literal, i.e., the body is not SQL-standard function body. Routines with a SQL-standard body are permitted starting from PostgreSQL 14. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-01-24 18:20 MIT License View
164 Unbalanced brackets Write expressions correctly. Find code fragments that have unbalanced brackets, i.e., the number of opening brackets is not the same as the number of closing brackets. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-01-24 16:02 MIT License View
165 Input parameters with the same name have different types Find named input parameters of routines that have the same name but different type. It could be a mistake or deliberate due to overloading of routines. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-01-24 15:01 MIT License View
166 Multiple deletes from the same table in a routine Code should be as compact as possible and system should make as little work as necessary in order to solve a task. Thus, instead of deleting different rows of the same table with different DELETE statements one should try to do it with one statement. The query excludes routines where IF statement is used. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-01-24 14:15 MIT License View
167 Multiple updates of the same table in a routine Code should be as compact as possible and system should make as little work as necessary in order to solve a task. Thus, instead of updating different fields of the same table with different UPDATE statements one should try to do it with one statement. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-01-24 13:59 MIT License View
168 Inconsistent use of older and newer join syntax in the subqueries of derived tables Find as to whether the subqueries of derived tables use both older join syntax (join condition in the where clause) and newer syntax (join condition in the from clause). Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-01-24 13:26 MIT License View
169 Unused indexes Find indexes that are not used by the DBMS. Remember that indexes are not a "free lunch" and they slow down the processes of updating data. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-01-19 00:23 MIT License View
170 The number of routines by the number of input parameters Find the number of user-defined non-trigger routines by the number of input parameters. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2024-01-16 09:43 MIT License View
171 The number of user-defined routines based on their database usage Find the number of non-extension routines in a database that modify data. The routines can be used to implement virtual data layer. Thus the queriy gives some indications about the possible extent of the layer. The query distinguishes trigger and non-trigger routines, thus it also gives information about triggers that modify data. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2024-01-15 00:38 MIT License View
172 The number of user-defined non-trigger routines by schema, by language, and by routine type Find the number of user-defined non-trigger routines based on their schema, language, and routine type. The routines can be used to implement virtual data layer. Thus the queriy gives some indications about the possible extent of the layer. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2024-01-14 20:53 MIT License View
173 The number of user-defined non-trigger routines by schema, by security type, and by being deterministic Find the number of user-defined non-trigger routines based on their schema, security type, and being deterministic. The routines can be used to implement virtual data layer. Thus the queriy gives some indications about the possible extent of the layer. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2024-01-14 20:52 MIT License View
174 The number of commented routines Find the number of commented routines based on the way how the comments have been added (as a COMMENT object or as a comment in the routine body). Sofware measure INFORMATION_SCHEMA+system catalog base tables 2024-01-14 20:49 MIT License View
175 The number of user defined triggers by different characteristics Find the number of user defined triggers by action orientation (ROW, STATEMENT), action timing (BEFORE, AFTER, INSTEAD OF), and event type (INSERT, UPDATE, DELETE) and their combinations. Sofware measure INFORMATION_SCHEMA only 2024-01-14 17:41 MIT License View
176 Updatable views with WHERE clause that do not have WITH CHECK OPTION constraint Find updatable views that restrict rows, i.e., have WHERE clause, but 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 2024-01-14 17:11 MIT License View
177 All updatable views Find all views through which it is possible to modify data in base tables. Is_insertable_into and is_updatable show as to whether the view is naturally updatable in PostgreSQL or has associated rules that make sure that INSERT/UPDATE/DELETE operations against the view will not cause an error. Please note that the rule could be DO INSTEAD NOTHING rule, i.e., data modification through the view does not cause an error but the data is not actually modified. If the updatability is achieved due to rules, then is_updatable=YES if the view has both UPDATE and DELETE rule that make sure that UPDATE and DELETE operations do not cause an error. General INFORMATION_SCHEMA only 2024-01-14 17:09 MIT License View
178 Different tasks of rules Find different tasks that are solved by using rules, i.e., different rules on the same table or different tables that do the same thing are considered to solve one task. General system catalog base tables only 2024-01-14 16:10 MIT License View
179 The number of rules by different characteristics Find the number of rules by event (SELECT, INSERT, UPDATE, and DELETE) and in total. Sofware measure system catalog base tables only 2024-01-14 16:10 MIT License View
180 Number of used tables Find statistics about how many derived tables have how many different underlying tables. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2024-01-14 13:43 MIT License View