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
21 Base table columns permitting empty strings and strings that consist of only whitespace characters (2) Find non-foreign key columns of base tables that have a textual type and do not have a simple CHECK constraint (i.e., a constraint that involves only one column) that seems to prohibit empty strings and strings that consist of only whitespace as well as a simple CHECK constraint that specifies permitted symbols. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-27 19:02 MIT License View
22 Perhaps unnecessay regular expression Find occurrences of possibly pointless regular expressions - i.e., value contains zero or more symbols. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-27 18:37 MIT License View
23 Perhaps a too long PL/pgSQL routine A large routine may have multiple tasks that should be split between multiple routines, each of which has a more focused task. Find the PL/pgSQL routines where the number of physical lines of code is bigger than 40. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-27 11:13 MIT License View
24 Routines with BOOLEAN return type that do not have a good name The prefic of the name should be "is_" or "has_" or "can_" (in English) or "on_" (in Estonian). Worse: check_rights. Better: has_rights. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-27 11:12 MIT License View
25 The usage of data type formatting functions Find expressions that use a data type formatting function - to_char, to_number, to_date, to_timestamp. General INFORMATION_SCHEMA+system catalog base tables 2024-12-25 19:57 MIT License View
26 Perhaps unnecessary Trim Find constraints where the use of Trim function is possibly unnecessary. CHECK constraint column!~'^[[:space:]]*$' already ensures that the values in the column cannot be empty strings or strings that consist of only whitespace. Trim(Column)!~'^[[:space:]]*$' - in this case the use of Trim function is unnecessary. Problem detection INFORMATION_SCHEMA only 2024-12-25 15:27 MIT License View
27 NOT IN or <> ALL in derived tables Avoid using NOT IN or <>ALL with a non-correlated subquery in PostgreSQL because the query performance will be very poor, especially in case of large data sizes. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-24 13:50 MIT License View
28 NOT IN or <> ALL in routines Avoid using NOT IN or <>ALL with a non-correlated subquery in PostgreSQL because the query performance will be very poor. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-24 13:50 MIT License View
29 Duplicate check of empty strings Find columns that have a check that prevents the empty string in the column but there is already another check on the column that enforces the constraint. If there is a constraint description!~'^[[:space:]]*$', then it covers the constraint description!='' and the latter becomes redundant. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-23 14:28 MIT License View
30 Perhaps is not a snake case - date, time, or by is not preceded by an underscore Find names that perhaps do not use the snake_case naming style because the name ends with the phrase "date", "time", "by" that is not preceded by an underscore. Prefer snake_case over PascalCase and camelCase in names. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-23 13:56 MIT License View
31 IS DISTINCT FROM should be used instead of <> in WHEN clauses Use a right predicate in trigger condition in order to ensure that the trigger executes always when it has to but not more often. IS DISTINCT FROM treats NULL as if it was a known value, rather than unknown. It would be relevant if a column that is referenced in the action condition is optional, i.e., permits NULLs. Problem detection INFORMATION_SCHEMA only 2024-12-23 12:29 MIT License View
32 Perhaps IS DISTINCT FROM should be used instead of <> in WHEN clauses Use a right predicate in trigger condition in order to ensure that the trigger executes always when it has to but not more often. IS DISTINCT FROM treats NULL as if it was a known value, rather than unknown. It would be relevant if a column that is referenced in the action condition is optional, i.e., permits NULLs. General INFORMATION_SCHEMA only 2024-12-23 12:27 MIT License View
33 Non-foreign key indexes that have been named as foreign key indexes Find indexes that are not on a foreign key column but the name of the index contains ixfk or idxfk. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-23 12:12 MIT License View
34 Perhaps updating of modification time is missing Find routines with SQL-standard body that seem to update data in a table that has a column for modification time but the routine does not seem to update the modification time while updating the row and the table does not seem to have an UPDATE trigger that changes the modification time. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-23 11:55 MIT License View
35 UPDATE triggers where WHEN clause has not been specified (the trigger could executed too often) Find UPDATE triggers where WHEN clause is not specified. These triggers could be executed too often because unneeded executions are not prevented. Problem detection INFORMATION_SCHEMA only 2024-12-23 09:54 MIT License View
36 UPDATE triggers where updated columns have not been specified (the trigger could executed too often) Find UPDATE triggers where updated columns are not specified. These triggers could be executed too often because unneeded executions are not prevented. Problem detection INFORMATION_SCHEMA only 2024-12-23 09:53 MIT License View
37 Perhaps is not snake_case - id, code, key, or nr is not preceded by an underscore Find names that perhaps do not use the snake_case naming style because the name ends with the phrase "id", "uuid", "code", "kood", "key", or "nr" that is not preceded by an underscore. Prefer snake_case over PascalCase and camelCase in names. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-23 09:26 MIT License View
38 A routine is invoked only once Find user-defined routines that are invoked by exactly one user-defined routine. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-22 14:53 MIT License View
39 Duplicate comments Find comments that have been registered with a COMMENT statement and that are associated with more than one object. It would probably mean that a comment is incorrect or missing. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-21 17:31 MIT License View
40 Gratuitous context in the names of schema objects Find schema objects that name starts with the schema name and then has at least one more symbol. "Shorter names are generally better than longer ones, so long as they are clear. Add no more context to a name than is necessary" (Robert C. Martin, Clean Code) Shema is a namespace. There cannot be in the same schema two schema objects that belong to the same name class and have the same name. Problem detection system catalog base tables only 2024-12-21 17:30 MIT License View