PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1040 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
161 Precision of a timestamp or a time column is too big Find columns with a timestamp or a time type where the precision (the permitted maximum number of fractional sections) is bigger than the precision in the default value of the column. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
162 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 2025-11-07 10:11 MIT License View
163 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 2025-11-07 10:11 MIT License View
164 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 2025-11-07 10:11 MIT License View
165 Redundant trim() function in whitespace constraints This query identifies superfluous trim() function calls within CHECK constraints where the validation is performed by a regular expression that disallows whitespace-only strings. A constraint using the pattern column !~ '^[[:space:]]*$' already provides comprehensive validation against empty or whitespace-only strings by anchoring the check to the start (^) and end ($) of the string. The trim() function is a pre-processing step that does not alter the boolean outcome of this specific regex match, making the expression trim(column) !~ '^[[:space:]]*$' functionally equivalent to the simpler column !~ '^[[:space:]]*$'. Removing the unnecessary function call improves clarity and simplifies the constraint. Problem detection INFORMATION_SCHEMA only 2025-11-17 13:26 MIT License View
166 Reference to the numeric type is too imprecise, i.e., precision and scale are missing Find base table columns that have the DECIMAL/NUMERIC type, but do not have precision and scale specified. "Specifying: NUMERIC without any precision or scale creates a column in which numeric values of any precision and scale can be stored, up to the implementation limit on precision. A column of this kind will not coerce input values to any particular scale, whereas numeric columns with a declared scale will coerce input values to that scale." Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
167 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
168 Referential degree of tables This metric represents the number of foreign keys in a base table. Sofware measure INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
169 Registration/modification time is not automatically set Find columns of base tables that name and type suggest that the column should contain the row registration time or last modify time but the column does not have a default value. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
170 Registration/modification time is not mandatory Find columns that contain registration or modification time but are optional. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
171 Semantic mismatch: non-textual data types for phone numbers This query identifies a semantic mismatch in data type selection for columns intended to store telephone numbers. It flags columns whose identifiers imply phone number content (e.g., names containing "phone", "mobile", "telef") but are defined with non-textual data types (e.g., INTEGER, NUMERIC, BIGINT). Telephone numbers are semantically strings, as they may contain leading zeros, international prefixes (+), and formatting characters (-, (), ext.), and are not subject to arithmetic operations. Storing them as numeric types leads to data loss (truncation of leading zeros) and formatting inflexibility. Problem detection INFORMATION_SCHEMA only 2025-11-27 10:35 MIT License View
172 Should the time zone be recorded in case of time or not? Find all the base table columns that have the type time without time zone or time with time zone. Return the data only if there is at least one column with the type time without time zone and one column with the type time with time zone. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
173 Should the time zone be recorded in case of timestamp or not? Find all the base table columns that have the type timestamp without time zone or timestamp with time zone. Return the data only if there is at least one column with the type timestamp without time zone and one column with the type timestamp with time zone. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
174 Stating the obvious (column names) Find the names of columns where the name of the column contains a part of the name of the data type of the column. For instance, the query finds columns, were the name contains fragments integer_ or _integer. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
175 Storing a duration as time Find columns of base and foreign tables that based on the column names are used to register durations but the type of the column is time. "It is possible to use a TIME data type if the duration is less than 24 hours, but this is not what the type is intended for, and can be the cause of confusion for the next person who has to maintain your code." Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
176 Storing a duration rather than a point in time Find columns of base and foreign tables that based on the column names and types are used to register start time and duration rather than start time and end time. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
177 Storing file content in the database Find columns that probably store content of files in the database. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
178 System-generated domain CHECK constraint names Find the names of domain CHECK constraints that have been system-generated. Names should follow the same style. If there is a mix of system-generated and user-defined names, then the style is most probably different. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
179 Table check constraints with regular expressions Find all CHECK constraints (except NOT NULL) that are associated with a base table or a foreign table column and use a regular expression. It is useful to enforce as many constraints at database level as possible. In this way one improves data quality as well as gives extra information to the database users (including the DBMS engines, development environments, and applications). General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
180 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