PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1053 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
681 Perhaps the type of a base table column/domain should be BOOLEAN (based on CHECK constraints) This query identifies base table columns and domains that utilize CHECK constraints to simulate boolean logic on non-boolean data types. It targets constraints that restrict the domain of permitted values to binary sets, such as {0, 1}, {'Y', 'N'}, {'T', 'F'}, or string literals like 'true'/'false'. While functional, this "pseudo-boolean" pattern is considered suboptimal in PostgreSQL. The native BOOLEAN data type is preferred for its storage efficiency, semantic clarity, and built-in support for logical operators, rendering such manual constraints unnecessary. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-14 14:37 MIT License View
682 Perhaps the type of a base table column/domain should be BOOLEAN (based on types and default values) Find base table columns and domains that have a textual type and the default value that represents a truth-value. For instance, the type of a column could be VARCHAR and the column has the default value 'TRUE'. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
683 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
684 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
685 Perhaps the type of a base table column/domain should be SMALLINT (based on classifiers) Find columns that name points to the possibility that values in this are classifier codes. The column has a numeric type but it is not SMALLINT. Usually each classifier type has so few values that type SMALLINT would be appropriate. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
686 Perhaps the type of a base table column/domain should be temporal (based on default values) Find base table columns and domains that have a textual type but the default value that represents a temporal value (either a static value or invocation of a function that returns such value). Specify for each column/domain a right data type that takes into account expected values in the column/domain. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
687 Perhaps the type of a base table column/domain should be temporal (deadlines) Find base table columns that name refers to the possibility that there are registered deadlines but the column does not have a temporal type. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
688 Perhaps the type of a base table column/domain should be VARCHAR (based on column names) Find base table columns that have CHAR type, where character maximum length is bigger than 1 and the name of the column does not refer to the possibility that the column holds some kind of codes or flags or hash values. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
689 Perhaps the type of a base table column should be an integer type (based on column names) Find columns of base tables where the name of the column has prefix or suffix "id" or has the name "id" but the column does not have an integer type or uuid type. A convention is to use the phrase "id" in the names of surrogate key columns. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
690 Perhaps the type of a base table column should be BOOLEAN (based on column names) This query identifies a semantic mismatch between a column's name and its data type. It flags base table columns that adhere to a predicate-based naming convention (i.e., starting with is_, has_, can_, or on_) but are not defined with the BOOLEAN data type. This is a design flaw as it forces developers to infer and manage truthiness through other types (e.g., INTEGER, CHAR(1)), which undermines schema clarity, requires data type coercion in queries, and can compromise data integrity by permitting non-boolean states. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-13 14:07 MIT License View
691 Perhaps the type of a base table column should be BOOLEAN (based on enumerated types) Find base table columns that have an enumerated type that seems to emulate Boolean type. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
692 Perhaps the type of a base table column should be TIMESTAMPTZ This query identifies all base table columns defined with the timestamp without time zone data type. Use of this data type is a potential design flaw as it can lead to ambiguity and bugs when handling data from multiple time zones. Problem detection INFORMATION_SCHEMA only 2025-11-07 12:21 MIT License View
693 Perhaps the type of a base table column should be TSTZRANGE This query identifies all base table columns defined with the range of timestamp without zone data type. Use of this data type is a potential design flaw as it can lead to ambiguity and bugs when handling data from multiple time zones. Problem detection INFORMATION_SCHEMA only 2025-11-07 12:24 MIT License View
694 Perhaps the type of a base table column should be XML, JSON, or JSONB (based on column names) Find base table columns that name refers to the possibility that these are used to register XML/JSON values. Find the columns that do not have an appropriate data type (xml, json, jsonb). One shouldn't use columns with a textual type to register such data. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
695 Perhaps the type of a parameter should be BOOLEAN (based on parameter names) Find routine parameters that based on the name seem to hold truth values. Find parameters that name starts with "is_" or "has_" or "can_" or "on_" and that do not have Boolean type. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
696 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 2025-11-07 10:11 MIT License View
697 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
698 Perhaps too many input parameters Too many parameters (in this case four or more) could be a sign of not separating concerns and having a routine that has more than one task. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
699 Perhaps too many square brackets Character classes are surrounded by two pairs of square brackets. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
700 Perhaps too many subconditions in a CHECK constraint Find check constraints of base table and foreign table columns that are either associated with more than one column and have at least one AND operation or are associated with exactly one column and have two or more AND operations. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View