PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1024 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 Potentially missing PRIMARY KEY or UNIQUE constraints (based on column names) Find columns of base tables that name refers to the possibility that it contains unique values but the column does not belong to any PRIMARY KEY/UNIQUE constraint. If something has to be unique, then it must be said to the system so that it could use the information for internal optimizations and enforce the constraint. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
622 Potentially missing PRIMARY KEY or UNIQUE constraints (based on foreign keys) All the keys must be enforced. Find base tables that implement M:N relationship types and that allow multiple relationships of the same type between the same entities. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
623 Potentially missing PRIMARY KEY or UNIQUE constraints (based on sequence generators) Find columns of base tables that contain automatically generated unique values but do not belong to any PRIMARY KEY/UNIQUE constraint. If something has to be unique, then it must be said to the system so that it could use the information for internal optimizations and enforce the constraint. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
624 Potentially missing PRIMARY KEY or UNIQUE constraints (based on UUIDs) If something has to be unique, then it must be said to the system so that it could use the information for internal optimizations and enforce the constraint. Find columns that contain Universally Unique Identifiers but are not a part of any simple PRIMARY KEY/UNIQUE constraint and are also not part of a foreign key. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
625 Potentially missing sequence generators (based on column names and types) Find surrogate key columns that do not have an associated sequence generator. Surrogate key values must be generated by using the system (the sequence generator mechanism in case of PostgreSQL). If there are no sequence generators, then there is a question as to whether there are no surrogate keys in the database at all (could be possible and OK) or (more probable) developers have forgotten to implement the generation of surrogate keys. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
626 Potentially unnecessary use of sequence generators Find simple natural key columns that are associated with a sequence generator. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
627 Precise comparison with pattern matching in CHECK constraints Find CHECK constraints that use precise comparison (= or <>) with a regular expression or LIKE pattern. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
628 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 2025-11-07 10:11 MIT License View
629 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 2025-11-07 10:11 MIT License View
630 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 2025-11-07 10:11 MIT License View
631 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 2025-11-07 10:11 MIT License View
632 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 2025-11-07 10:11 MIT License View
633 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 2025-11-07 10:11 MIT License View
634 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 2025-11-07 10:11 MIT License View
635 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 2025-11-07 10:11 MIT License View
636 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 2025-11-07 10:11 MIT License View
637 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 2025-11-07 10:11 MIT License View
638 PUBLIC has the USAGE privilege of a schema Find schemas where PUBLIC has the usage privilege. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
639 Range lower bound can be NULL This query identifies columns of base tables that use a RANGE data type but are configured to permit a NULL lower bound. This highlights ranges that can be "unbounded" on their starting side, which may be unintentional and could impact query logic and data constraints. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 12:03 MIT License View
640 Range lower bound is not restricted This query finds range columns of base tables that are missing a safety check on their starting value. It looks for columns where the start of the range can be set to any value, without rules to ensure that value makes sense. This helps ensure that all ranges have proper limits defined for their starting points. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 12:03 MIT License View