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
621 Predefined character classes must be between double square brackets Find regular expressions that do not have predefined character classes between double square brackets, e.g., [:digit:] instead of [[:digit:]]. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-22 17:34 MIT License View
622 Prefer Polymorphism to If/Else or Switch/Case Find routines with IF/ELSE or SWITCH/CASE statements. If your routine has a multipart IF/CASE statement, then perhaps it has multiple tasks and it violates the separation of concerns and single responsibilities principles. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-29 13:31 MIT License View
623 Prefer Polymorphism to If/Else or Switch/Case (2) Find routines with multiple raise exception commands. Perhaps it has multiple tasks and it violates the separation of concerns and single responsibilities principles. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-29 13:39 MIT License View
624 Prefixes in the names of database objects Find for different types of database objects all the prefixes that are used in different names. One should be consistent in naming, including in the use of prefixes. If you use prefix in the name of a database object, then it could refer to the type of the database object. Do not use different prefixes in the names of database objects that have the same type. General INFORMATION_SCHEMA+system catalog base tables 2024-12-21 16:22 MIT License View
625 Primary key columns are not the first in a table In SQL tables each column has the ordinal position. Find all the base tables where the primary key columns are not the first in the table, i.e., there is at least one non-primary key column that comes before a primary key column. It is easier to grasp the primary key if its columns are the first in the table. It could be that a table inherits from an abstract table where no keys have been defined. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-10-16 10:33 MIT License View
626 Privileges to execute routines Find privileges to execute routines that have been given to non-superusers. Check as to whether it conforms to the principle of least privilege. Check that users that correspond to applications have all the necessary privileges. Users (applications) should use a database through virtual data layer. Thus, if they need to modify data in the database (in case of table functions read data), then they must execute a routine. General INFORMATION_SCHEMA+system catalog base tables 2020-12-29 10:38 MIT License View
627 Privileges to use base table columns If you do give privileges to base tables, then these should follow the principle of least privilege - the smallest possible amount of privileges to the smallest possible set of columns General INFORMATION_SCHEMA+system catalog base tables 2020-12-29 10:38 MIT License View
628 Privileges to use base tables Users (applications) should ideally use a database through virtual data layer and thus not directly use base tables. If there is a need to provide direct access to the base tables, then one should grant access based on the principle of least privilege, i.e., to the minimum possible number of base tables. General INFORMATION_SCHEMA+system catalog base tables 2020-12-29 10:38 MIT License View
629 Privileges to use views Find privileges to use views. Check as to whether it conforms to the principle of least privilege. Check that users that correspond to applications have all the necessary privileges. Users (applications) should use a database through virtual data layer. Thus, if they need to read data from a database, then they should use views. General INFORMATION_SCHEMA+system catalog base tables 2020-12-29 10:38 MIT License View
630 Procedures cannot have START TRANSACTION and SAVEPOINT You cannot use a START TRANSACTION or a SAVEPOINT statement in a procedure. Procedures appeared in PostgreSQL 11. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-04 11:50 MIT License View
631 PUBLIC has the USAGE privilege of a schema Find schemas where PUBLIC has the usage privilege. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-23 17:52 MIT License View
632 Reasonable upper bound to the length of textual values is missing Find non-foreign key base table columns that are not used to record comments/descriptions/explanations etc. and that have TEXT or VARCHAR type without restrictions to the field size (field size in case of VARCHAR or a CHECK constraint). Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-12-16 12:32 MIT License View
633 Recursive triggers that directly modify their home table Do not cause potentially infinite loops. Recursive trigger fire themselves over and over again. If the system is not able to stop these, then it eventually consumes all the resources of the system. Although the system is able to detect these it is better to avoid creating these altogether. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
634 Regular expression with possibly a LIKE pattern Find expressions that use a regular expression with a like predicate pattern. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-10-21 11:11 MIT License View
635 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
636 Routine body only in uppercase Uppercase means screaming and having code entirely in uppercase makes its reading more difficult. On the other hand, it would be a good idea to have keywords in uppercase. Find routines that body contains a SQL data manipulation statement (which shouldn't be entirely in uppercase) but still the body is completely in uppercase. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-04 20:12 MIT License View
637 Routine body with ordering the query result based on positional references Find routines where the query result is sorted based on the column number in the SELECT clause. Such query is sensitive towards changing the order of columns in the SELECT clause, i.e., if one changes the order of columns in the SELECT clause, then one must change the numbers in the ORDER BY clause as well, otherwise the query will produce undesired order of rows. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-10-31 15:23 MIT License View
638 Routine for reading data uses another routine to read some data Find routines that only read data but invoke some other routine to read some more data. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-10 17:10 MIT License View
639 Routines that can be invoked with a variable number of arguments Find routines with a VARIADIC parameter. These are routines that take as input an undefined number of arguments where the argument that is an undefined number are all of the same type and are the last input arguments. General INFORMATION_SCHEMA+system catalog base tables 2021-11-04 12:37 MIT License View
640 Routines that use old syntax for limiting rows Find PL/pgSQL routines and SQL routines that do not have SQL-standard body that use unstandardized LIMIT clause instead of standardized FETCH FIRST n ROWS clause. The query excludes routines that are a part of an extension. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-10-29 11:34 MIT License View