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
121 Insufficient number of user-defined foreign tables This query assesses the utilization of PostgreSQL's Foreign Data Wrapper (FDW) capabilities. It verifies the existence of at least 2 user-defined foreign tables within the database schema. This requirement ensures that the implemented solution demonstrates the ability to integrate and query data from external sources, extending the data model beyond local storage. Problem detection INFORMATION_SCHEMA only 2025-11-30 09:31 MIT License View
122 Insufficient number of user-defined views This query assesses the use of abstraction layers within the database schema. It verifies that there are at least 4 user-defined views present. Views are essential for simplifying complex queries, restricting data access, and presenting specific data perspectives to applications. Meeting this threshold indicates a sufficient implementation of data abstraction and query encapsulation. Problem detection INFORMATION_SCHEMA only 2025-11-30 09:38 MIT License View
123 IS DISTINCT FROM should be used instead of <> in WHEN clauses Use a right predicate in trigger condition in order to ensure that the trigger executes always when it has to but not more often. IS DISTINCT FROM treats NULL as if it was a known value, rather than unknown. It would be relevant if a column that is referenced in the action condition is optional, i.e., permits NULLs. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
124 IS NULL check is probably not needed Find CHECK constraints that refer only to mandatory columns but the Boolean expression has IS NULL condition. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
125 JSON type instead of JSONB type "In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys." (https://www.postgresql.org/docs/current/datatype-json.html) Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
126 Maximum field size of code columns This query determines the maximum field size of text columns whose names suggest they store some type of code. General INFORMATION_SCHEMA only 2026-05-07 19:08 MIT License View
127 Missing default values for audit timestamps This query identifies base table columns whose name and data type suggest they store row registration or last modification times, but which lack a default value. In good database design, such audit timestamp columns should typically have a default value (like CURRENT_TIMESTAMP or now()) to ensure the time is recorded automatically. Problem detection INFORMATION_SCHEMA only 2026-05-26 07:13 MIT License View
128 Mixing different mechanisms to generate surrogate values Use the same mechanism of generating surrogate key values throughout the database. The use of SERIAL notation/explicitly creating a sequence generator and declaration of a column as an identity column will cause the creation of an external and internal sequence generator, respectively. Nevertheless, one should try to stick with using one of the mechanisms in order to cause less confusion. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code) Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
129 Mixing the use of TEXT and VARCHAR type in case of base table columns Declaring a column to have the type TEXT or the type VARCHAR (without the maximum number of characters) has the same end result in terms of what data can be recorded in the column. Nevertheless, one should try to stick with using one of the type names in order to cause less confusion. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code) Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
130 Multiple columns in the same base table that are associated with a sequence generator Find base tables where multiple columns are associated with a sequence generator. Do not create unnecessary sequence generators. If one uses in a table a surrogate key, then it is enough to have one column where the values are generated by using a (external or internal) sequence generator. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
131 Non-foreign key columns that have no associated CHECK constraints Find what are the base table columns that are not foreign key columns and that have no associated CHECK constraints? Perhaps some CHECK constraints are missing. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
132 Non-native data types for network addresses This query identifies base table columns intended to store IP or network addresses that are not using PostgreSQL's native inet data type. It locates these columns by searching for specific English and Estonian naming patterns (such as 'ip', 'network', 'addr', or 'aadr'). Storing network addresses as generic text is an anti-pattern, as the inet type provides built-in validation, specialized network functions, and efficient indexing. Problem detection INFORMATION_SCHEMA only 2026-05-09 13:06 MIT License View
133 NOT NULL constraint via CHECK instead of NOT NULL constraint Find columns where the NOT NULL constraint has been added with the help of an explicit CHECK constraints instead of a NOT NULL constraint. One should note that internally NOT NULL constraints are treated as a kind of CHECK constraints. Nevertheless, the design should be consistent (i.e., NOT NULL is enforced with the same way in different places). Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
134 Nullable timestamps missing an infinity default This query identifies optional (nullable) timestamp columns in base tables that lack a default value. It highlights fields that might represent open-ended time periods (such as expiration or end dates). In such cases, it is often a better practice to assign the special value 'infinity' as the default, rather than relying on NULL values. Problem detection INFORMATION_SCHEMA only 2026-05-26 14:45 MIT License View
135 Optional base table columns Find optional base table columns, i.e., columns that permit NULLs. Are you sure you want to allow NULLs in these columns? General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
136 Optional base table columns that have a default value This query identifies columns that are both NULLable and have a DEFAULT value. This configuration represents a semantic contradiction: the DEFAULT clause implies that a value should always exist for the column, while the absence of a NOT NULL constraint explicitly permits the absence of a value. The presence of a DEFAULT strongly suggests the column's business logic requires a value, and therefore it should be defined with a NOT NULL constraint to enforce this consistently and make the schema's intent unambiguous. Problem detection INFORMATION_SCHEMA only 2025-11-12 19:04 MIT License View
137 Optional columns before mandatory columns Find cases where optional columns are before a mandatory column. Place mandatory columns before optional columns to improve comprehensibility of the table. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
138 Optional foreign key columns Find foreign key columns that do not have the NOT NULL constraint. It is better to limit the use of NULLs and optional columns due to the problems that it causes in interpreting the query results, making queries, and enforcing constraints. In addition, one should check as to whether this kind of design is consistent with the multiplicities in the conceptual data model. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
139 Overly strict CHECK constraints on personal names This query identifies potentially overly strict CHECK constraints applied to base or foreign table columns that store personal names. It highlights validation rules that might inadvertently block valid legal names. Problem detection INFORMATION_SCHEMA only 2026-06-03 12:28 MIT License View
140 Pairs of base tables that have at least two columns with the same names and data types What are the pairs of base tables that have at least two columns with the same names and data types. The tables might violate the principle of orthogonal design and hence might facilitate uncontrolled data redundancy over different tables. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View