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
701 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
702 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
703 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
704 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
705 Prefixes of base table names Find base tables that name starts with a prefix. Do not use prefixes in case of base table names. Derive the names from the names of entity types. Do not use "_", "t_", "tab_", "t11_" etc as prefixes of a table. Problem detection INFORMATION_SCHEMA only 2022-11-15 16:32 MIT License View
706 Preventing strings that consist of only spaces instead of strings that consist of only whitespace characters Find columns of base tables and foreign tables where one uses a check constraint to prevent values that consist of only spaces. Make sure that this is the correct constraint and there is no need to prevent values that consist of only whitespace characters. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View
707 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
708 Privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers Find privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that are not for the owner and not for the systemic user postgres. Make sure that there is the right amount of privileges for each and every relevant user. General system catalog base tables only 2021-03-07 20:57 MIT License View
709 Privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that have been granted to a superuser Find privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that have been granted to a superuser. Superuser can do anything in the database and thus does not need the privileges. The result is a sign that perhaps the executed GRANT statements were incorrect (wrong username) or the grantee later got superuser status (that it shouldn't have). Problem detection system catalog base tables only 2022-10-21 15:53 MIT License View
710 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
711 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
712 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
713 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
714 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
715 Publications with no tables Find publications that do not contain any table. Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View
716 PUBLIC has TEMPORARY privilege in the database Find as to whether PUBLIC (all current and future users) has TEMPORARY privilege in the database. PUBLIC gets the privilege by default. Problem detection system catalog base tables only 2021-12-31 15:52 MIT License View
717 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
718 Publicly accessible system catalog tables Find privileges to use system catalog base tables or views that have been granted to public. General INFORMATION_SCHEMA only 2023-11-24 14:56 MIT License View
719 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
720 Recursive relationships with the same source and target Find incorrectly implemented adjacency lists. Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View