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
281 Base table column of surrogate key values does not have an integer data type (based on column names) Find base table columns that belong to a primary key, unique, or foreign key constraint and that name refers to the possibility that these are used to hold surrogate key values. Find the columns where the data type of the column is not an integer type or uuid. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-13 12:10 MIT License View
282 Unsecure SECURITY DEFINER routines SECURITY DEFINER routines must be secured against the malicious use of pg_temp schema. Find routines that do not explicitly set the search path or do it incorrectly (the search path is between quotation marks) and are thus potential targets of the attack. pg_temp must be the last entry in search_path. The lack of search_path is allowed only if the SQL statements in routines explicitly refer to the schemas that contain the schema objects. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-13 12:07 MIT License View
283 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
284 Candidate keys where all columns have a static default value Find base table primary key and unique constraints where all columns probably have a static default value. Candidate key columns (columns that belong to the primary key or to an unique constraints) shouldn't have static default values unless you are enforcing a rule that a table can have at most one row. The point of default values is that system assigns them automatically. There cannot be multiple rows with the same key value. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-12 11:33 MIT License View
285 Candidate key columns that have a static default value Find base table columns that are covered by a primary key or a unique constraint and that probably have a static default value. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-12 11:32 MIT License View
286 Perhaps the type of a base table column/domain should be VARCHAR (based on column names) Find base table columns that have CHAR type, where character maximum length is bigger than 1 and the name of the column does not refer to the possibility that the column holds some kind of codes or flags or hash values. Problem detection INFORMATION_SCHEMA only 2023-11-12 10:48 MIT License View
287 Perhaps an unsuitable use of CHAR(n) type in base tables Find non-foreign key base table columns with the type CHAR(n) where n>1 that are not meant for storing codes or hash values. CHAR(n) is suitable for storing values that have a fixed length (for instance, country code according to the ISO standard). In case of variable length strings the end of the stored string is padded with spaces. Thus, for instance, do not use CHAR(n) in case of columns for storing names, comments, descriptions, e-mail addresses etc. Hash values have a fixed length that depends on the used hash function. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-12 10:46 MIT License View
288 Column names that make joining more difficult (foreign key column name contains the table name) Find foreign keys where the name of the foreign key column and the corresponding primary key/unique column is different but the difference comes from the fact that the foreign key column name starts or ends with the table name. Simplify writing queries that have to join data from multiple base tables. If the columns that participate in the join condition have the same name, then one can use the USING syntax. Problem detection system catalog base tables only 2023-11-12 10:26 MIT License View
289 Number of tables covered by derived tables Find the number of base tables, the number of base tables that are referred from at least one derived table (view or materialized view), the number of base tables that are referred from at least one view, and the number of base tables that are referred from at least one materialized view. If the database is used through the public database interface (virtual data layer), then, ideally, each table is referred from the subquery of at least one derived table. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2023-11-11 09:33 MIT License View
290 Coverage by derived tables Find for each base table the list of derived tables (both views and materialized views) that refer to the base table. If the database is used through the public database interface (virtual data layer), then, ideally, each table is referred from the subquery of at least one derived table. General INFORMATION_SCHEMA+system catalog base tables 2023-11-11 09:31 MIT License View
291 Number of underlying tables of derived tables Find for each view or materialized view the number of tables based on which the derived table has been directly defined. These tables could be base tables or derived tables. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2023-11-11 09:30 MIT License View
292 Useless type indication (2) Find columns and parameters where the type of the identifier is perhaps explicitly indicated in the name. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-10 14:55 MIT License View
293 Useless type indication Find columns and parameters where the type of the identifier is perhaps explicitly indicated in the name. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-10 14:47 MIT License View
294 Inconsistent use of length and char_length functions Find as to whether both functions length and char_length are used in the database. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-10 14:38 MIT License View
295 Perhaps USING syntax could be used for joining in the subqueries of derived tables Find derived tables that use newer join syntax where join conditions are written in the WHERE clause but do not use USING synatx. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-10 14:14 MIT License View
296 Perhaps brackets are missing in a regular expression that uses OR logical operation Find regular expressions where choice between alternatives has no brackets. Thus, instead of '(a|b|c)' there is 'a|b|c'. An example: '^a|b|c$' -permits in the string symbol "|" but '^(a|b|c)$' does not permit in the string symbol "|". Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-09 20:22 MIT License View
297 Deterministic (immutable) functions that do not have input parameters Find deterministic functions that do not have any input parameters. Make sure that it is correct because in general a deterministic function must calculate a value based on input. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-09 19:53 MIT License View
298 Columns of base tables that hold truth values that do not have a default value although they could have it (non-Boolean columns) Find columns of base tables that do not have type BOOLEAN but are used to record Boolean values. Based on column names these implement a state machine or record agreements. At the same time the columns do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. It should be possible to select one of these as the default value of the column. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-09 16:40 MIT License View
299 Columns of base tables that hold truth values that do not have a default value although they could have it (Boolean columns) Find columns of base tables that have type BOOLEAN. Based on column names these implement a state machine or record agreements. At the same time the columns do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. It should be possible to select one of these as the default value of the column. Problem detection INFORMATION_SCHEMA only 2023-11-09 16:39 MIT License View
300 Length and char_lenfgth functions are used within the same expression Find expressions that refer to both length and char_length functions. Make sure that the expression is correct. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-09 13:44 MIT License View