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
181 Do not use the money data type Find base table columns with the Money data type. Each value of the money type has associated currency sign that depends on server settings. It could be $. Moreover, using the values for arithmetic operations requires casts that makes the code more complicated. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
182 Tsvector values are not automatically calculated Find base table columns with tsvector type in case of which it is not a generated column nor does the table has an associated trigger to calculate the tsvector value automatically. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
183 Base table column of national identification numbers has a too short field size Find base table columns with VARCHAR type that name refers to the possibility that these are used to register national identification numbers (personal codes). Find the columns where the field size is shorter than 20. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
184 Candidate keys where all columns have a static default value Find base table primary key and unique constraints where all columns probably have a static default value. Candidate key columns (columns that belong to the primary key or to an unique constraints) shouldn't have static default values unless you are enforcing a rule that a table can have at most one row. The point of default values is that system assigns them automatically. There cannot be multiple rows with the same key value. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
185 At most one row is permitted in a table (based on check constraints) Find base tables and foreign tables where based on a check constraint, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a check constraint permits only one possible value in a column, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
186 At most one row is permitted in a table (based on enumeration types) Find base tables and foreign tables where based on the type of a column, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a column has an enumeration type with exactly one value, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
187 Base tables and materialized views without any index Find base tables and materialized views that do not have any index. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
188 Base table columns permitting temporal values that may be outside the range of logical values Find base tables columns with temporal types (date and timestamp) that do not belong to a foreign key and that do not have any associated simple CHECK constraints, i.e., constraint that involves only one column. For instance, in the column registration_time that does not have any associated CHECK constraints could be values '1200-01-01 00:00' or '5900-12-31 00:00'. Rows with these values most probably represent wrong propositions and the system should restrict registration of such data. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
189 Minimum tuple length required before trying to move long column values into TOAST tables has been changed Find base tables in case of which toast_tuple_target storage parameter value is not the default value (2040). "Changing this value may not be useful for very short or very long rows. Note that the default setting is often close to optimal, and it is possible that setting this parameter could have negative effects in some cases. " Make sure that the parameter has an optimal value. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
190 Base tables where certainly registration time is not recorded Find base tables that do not have any column with a timestamp type. In such tables certainly registration time is not recorded. Make sure as to whether recording registration time is necessary. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
191 Base tables that do not have a TOAST table Find base tables that (due to the types of their columns) do not have an associated TOAST table for storing out-of-line data. General system catalog base tables only 2025-11-07 10:11 MIT License View
192 Base tables with plenty of data Find base tables that have 1000 rows or more. General system catalog base tables only 2025-11-07 10:11 MIT License View
193 Perhaps last update time trigger is missing Find base tables that have a column for last update time but the table does not have associated before update row level trigger for changing the last update time. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
194 Perhaps a CHECK constraint about the combination of truth values is missing Find base tables that have at least two columns that have Boolean type and have at least one Boolean column that is not covered by a CHECK constraint involving more than one Boolean column. The Boolean columns possibly mean that we want to record data about states. Often the states depend on each other. For instance, if an order is archived it must be inactive. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
195 Perhaps a CHECK constraint about the order of events is missing Find base tables that have at least two columns that have DATE or TIMESTAMP (with or without time zone) type and do not have any associated CHECK constraint that involves two or more of these columns. The columns mean that we want to record data about events or processes, which often have a certain order. Hence, in case of each row of such a table the values in these columns must be in a certain order. For instance, the end of a meeting cannot be earlier than the beginning of the meeting. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
196 Base tables created based on a type Find base tables that have been created based on a composite type and thnk through as to whether it was really needed. General system catalog base tables only 2025-11-07 10:11 MIT License View
197 Base tables that have more than five indexes Find base tables that have more than five indexes. Indexes can be used to increase the speed of queries (SELECT statements). However, the amount of indexes shouldn't be too large. Otherwise it may reduce the speed of operations that are used to modify data. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
198 Base tables with multiple temporal columns Find base tables that have more than one column with a temporal type (date or timestamp). General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
199 Base tables with multiple Boolean columns Find base tables that have more than one column with Boolean type. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
200 Duplicate stored generated base table columns Find base tables that have more than one stored generated column with the same expression. The support of generated columns was added to PostgreSQL 12. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View