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
221 Routine body has keywords that are not in uppercase Keywords in uppercase improve readability. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-24 17:06 MIT License View
222 Duplicate specification of character classes Find regular expressions where within the same specification of a character class the character class alnum as well as 0-9, \d, A-Z, or a-z has been defined. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-24 10:43 MIT License View
223 Perhaps unnecessary DECLARE section in a PL/pgSQL routine (2) Find PL/pgSQL routines that perhaps unnecessarily contain DECLARE section. More specifically, find routines with the DECLARE section where the keyword DECLARE is followed by BEGIN, i.e., the DECLARE section is empty. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-23 13:39 MIT License View
224 Invocation of a system-defined routine without providing any arguments Find user-defined routines that contain an invocation of a system-defined function without providing any argument. The query considers all aggregate functions as well as some popular scalar functions. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-22 13:00 MIT License View
225 Columns with tsvector type Find columns of base tables and materialized views that have tsvector type. General INFORMATION_SCHEMA+system catalog base tables 2023-12-22 12:41 MIT License View
226 Data type usage in the base table columns Get overview of used data types in the columns of base tables. If the selection is very small then this is a warning sign that perhaps unsuitable types have been used. General INFORMATION_SCHEMA only 2023-12-22 12:39 MIT License View
227 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
228 All rules Find user-defined rules for rewriting data manipulation language statements. Rules should be used only for the tasks that cannot be achieved in a declarative manner, i.e., for example, by declaring a constraint. General system catalog base tables only 2023-12-22 12:30 MIT License View
229 UPDATE triggers Find all UPDATE triggers. Make sure that they specify a correct set of columns in which data modification will fire the trigger. General INFORMATION_SCHEMA only 2023-12-22 09:47 MIT License View
230 Unnamed columns in routines Find user-defined routines that contain a SQL statement that does not give the name to a column in a SQL statement. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-22 00:04 MIT License View
231 Perhaps a reference to the variable NEW is missing Find row level before insert and before update triggers that only task is not to raise an exception and where the variable NEW is not used in the trigger routine outside the RETURN clause. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-21 20:34 MIT License View
232 Perhaps a reference to the variable OLD is missing Find row level before delete triggers that only task is not to raise an exception and where the variable OLD is not used in the trigger routine outside the RETURN clause. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-21 19:29 MIT License View
233 User-defined routines that read data Find user-defined routines that contain SELECT … FROM or PERFORM … FROM operations. PostgreSQL uses multiversion concurrency control (MVCC). Therefore, SELECTs do not block modifications and vice versa. One has to take steps to achieve correct behaviour of data access code. In addition, one should not ask data with multiple queries if it is possible to achieve the result with only one query. General INFORMATION_SCHEMA+system catalog base tables 2023-12-21 15:00 MIT License View
234 Multiple RETURNING clauses in a SQL function Find SQL functions that have multiple statements with the RETURNING clause. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-21 13:59 MIT License View
235 Columns of derived tables that name has been given by the system (2) Find columns of derived tables (i.e., views and materialized views) where in the creation statement of the table the name of the column has not been specified, i.e., it is generated by the system. Problem detection system catalog base tables only 2023-12-21 12:17 MIT License View
236 Columns of derived tables that name has been given by the system Find columns of derived tables (i.e., views and materialized views) where in the creation statement of the table the name of the column has not been specified, i.e., it is generated by the system. Problem detection system catalog base tables only 2023-12-21 12:15 MIT License View
237 Deterministic (immutable) functions that do not return a value Find deterministic (immutable) functions that do not return a value. This goes against the idea of deterministic functions. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-20 19:33 MIT License View
238 Base table columns permitting temporal values that may be outside the range of logical values Find base tables columns with temporal types (date and timestamp) that do not belong to a foreign key and that do not have any associated simple CHECK constraints, i.e., constraint that involves only one column. For instance, in the column registration_time that does not have any associated CHECK constraints could be values '1200-01-01 00:00' or '5900-12-31 00:00'. Rows with these values most probably represent wrong propositions and the system should restrict registration of such data. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-17 00:40 MIT License View
239 Not equals check in unstandardized way Find user-defined routines that use != operator to test as to whether two values are not equal. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-13 14:16 MIT License View
240 Perhaps Trim is missing Find derived tables (views and materialized views) that apparently concatenate values (by using || operator or use Concat function or use Format function) by putting a space between these but do not use Trim function to get rid of the extra space at the beginning or the end of the string. Problem detection system catalog base tables only 2023-12-13 13:53 MIT License View