PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1036 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
801 Perhaps too many different suffixes in the names of database objects that have the same type One should be consistent in naming, including in the use of suffixes. If you use sufix in the name of a database object, then it should refer to the type of the database object. Do not use different suffixes in the names of database objects that have the same type. Find types of database objects in case of which there are different suffixes in different names. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
802 The maximum number of characters may be missing Perhaps the character maximum length has been omitted accidentally, i.e., one wrote VARCHAR instead of VARCHAR(n) where n is the maximum permitted number of characters in the field value. VARCHAR and TEXT are synonyms. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
803 Numeric literals between apostrophes Placing numeric literals between apostrophes will cause unnecessary type conversions. It could also be that the literal should indeed be textual but the problem is in choosing the values. For instance, table Occupation has column occupation_code with the type VARCHAR(3). However, all the values in the column consist of digits (for instance, 1, 2, 3). Thus, it would have been better to a) use SMALLINT as the column type or b) use different occupation codes that contain additional symbols to digits. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
804 Unnecessary usage privileges of PUBLIC PostgreSQL gives by default some privileges to all the present and future database users (PUBLIC). Find usage privileges of collations, domains, foreign data wrappers, foreign servers, and sequences that have been given to PUBLIC. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
805 Invalid character class PostgreSQL regular expressions do not have character classes word and letter. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
806 Referential degree of a schema Referential degree of a schema is defined as the number of foreign keys in the database schema. Sofware measure INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
807 Case insensitive search Refers to the column pg_proc.prokind and thus works starting from PostgreSQL 11. Find user-defined routines and derived tables (views/materialized views) that have a subquery with case insensitive search (by using the upper or lower function or ILIKE predicate or (?i) modifier of a regular expression pattern). General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
808 Overloading may cause runtime error Routines in the same schema that have the same name and that have parameters with different types or different number of parameters are not considered to be in conflict at the creation time. However, if defaults are provided in the definition of parameters, then these routines might be conflict during runtime. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
809 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 2025-11-07 10:11 MIT License View
810 SELECT * in a routine body SELECT statement should list the columns not use SELECT * to return data from all the columns. Firstly, it ensures, that the query asks only data that is really needed by the routine. It means less data that the DBMS has to fetch and pass to the routine. It could also mean that the DBMS can answer to a query based on an index without reading table blocks. Secondly, it documents the data that is returned by the query. The query does not consider objects that are a part of an extension. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
811 A large number of triggers Show user-defined triggers if there are more than 9 different trigger routine bodies, i.e., different triggers on different tables that do the same thing count as one trigger. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
812 Perhaps the type of a base table column/domain should be numeric (based on default values) Specify for each column/domain a right data type that takes into account expected values in the column/domain. Find base table columns and domains that have a textual type but the default value that represents a number (for instance, '100', '2', or '0.22'). Exclude columns about formats. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
813 Perhaps the type of a base table column/domain should be INTEGER/SMALLINT/BIGINT (based on sequence generators) Specify for each column/domain a right data type that takes into account expected values in the column/domain. Find base table columns and domains that refer to the nextval function by using the default value mechanism but do not have the type INTEGER, SMALLINT, or BIGINT. This check is performed in case of identity columns: ERROR: identity column type must be smallint, integer, or bigint. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
814 Do not clone columns "Split a base table column into multiple columns based on the values in some other column. Each such newly created column has the name, a part of which is a data value from the original tables."(Bill Karwin) Find base tables that have more than one columns with the same type and field size and the difference between the columns are the year or month number at the end of the column name (two or four numbers, preceded by an underscore). Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
815 CHECK constraints that perhaps incorrectly consider 'infinity' and '-infinity' special values Such special values belong to the types DATE, TIMESTAMP, NUMERIC, REAL, and DOUBLE PRECISION. No value can be bigger than infinity or smaller than -infinity. If the check constraint cheks that a value must be bigger than -infinity or smaller than infinity, then it does not restrict (almost) anything. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
816 Table columns that are associated with a sequence generator Surrogate key values must be generated by using the system (the sequence generator mechanism in case of PostgreSQL). If there is no usage of 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. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
817 Base table columns with the type VARCHAR(1) The choice of data types should reveal as much as possible about the nature of the data in the column. The type of these columns could be CHAR(1) and they should have a constraint that a value in the column cannot be an empty string. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
818 Columns with exact/floating numeric types have textual default values The default value of a column should belong to the type of the column. The system shouldn't conduct unnecessary type casts. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
819 Too short table constraint names The names should be expressive. Find names of constraints, which are associated directly to a table, that are shorter than the length of the name of the table + two characters. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
820 Names of triggers and rules that do contain the table name The names should contain table name in order to make the names better understandable. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View