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
901 Perhaps too many different prefixes in the names of database objects that have the same type 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 should refer to the type of the database object. Do not use different prefixes in the names of database objects that have the same type. Find types of database objects in case of which there are different prefixes in different names. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-21 16:20 MIT License View
902 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 2024-12-21 17:16 MIT License View
903 Perhaps character class [[:alnum:]] could be used Perhaps regular expressions that refer to character classes alnum and digit could be simplified. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-11-02 09:32 MIT License View
904 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 2021-02-25 17:29 MIT License View
905 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 2021-11-04 13:20 MIT License View
906 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 2021-12-31 13:53 MIT License View
907 Invalid character class PostgreSQL regular expressions do not have character classes word and letter. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-04 12:50 MIT License View
908 Explicit locking PostgreSQL uses Multi-version Concurrency Control (MVCC) and thus, sometimes, one has to explicitly lock certain rows or entire table. One has to use LOCK TABLE or SELECT … FOR UPDATE statements for that. General INFORMATION_SCHEMA+system catalog base tables 2023-12-11 15:02 MIT License View
909 Explicit locking is missing PostgreSQL uses multiversion concurrency control (MVCC). SELECTs do not block modifications and vice versa. One has to take steps to achieve correct behaviour. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-13 14:57 MIT License View
910 Foreign keys with ON UPDATE CASCADE Referential constraints (foreign key constraints) that employ ON UPDATE CASCADE compensatory action. ON UPDATE CASCADE should only be used if the referenced key is a natural key (its values can be changed). General system catalog base tables only 2020-11-15 15:39 MIT License View
911 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 2020-11-13 11:30 MIT License View
912 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 2021-10-25 16:55 MIT License View
913 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 2021-10-25 16:11 MIT License View
914 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 2023-11-13 12:07 MIT License View
915 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 2024-12-13 14:34 MIT License View
916 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 2023-12-25 11:23 MIT License View
917 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 2023-12-30 10:59 MIT License View
918 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 2021-03-04 11:24 MIT License View
919 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 2022-11-28 15:15 MIT License View
920 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 2022-12-07 20:13 MIT License View