PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1031 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
301 Duplicate independent (i.e., not created based on a table) composite types Find composite types with the same attributes (regardless of the order of attributes). Make sure that there is no duplication. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
302 Duplicate keys Find completely overlapping key (primary key, unique, and exclude where all operators are =) constraints. This is a form of duplication. It leads to the creation of multiple indexes to the same set of columns. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
303 Duplicate materialized views Find materialized views with exactly the same subquery. There should not be multiple materialized views with the same subquery. 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 system catalog base tables only 2025-11-07 10:11 MIT License View
304 Duplicate non-fuction based unique indexes Find pairs of non-function based unique indexes that cover the same set of columns. Include indexes that support a constraint (primary key, unique, exclude), i.e., these indexes have been automatically created due to the constraint declaration. For instance, it helps us to find unique indexes that have been defined to already unique columns. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
305 Duplicate NOT NULL constraints Find columns that have NOT NULL constraint through a domain and also directly. Do not duplicate NOT NULL constraints in orde to avoid confusion and surprises. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
306 Duplicate removal of duplicates in derived tables Find derived tables (views and materialized views) that contain both DISTINCT and GROUP BY. Make sure that the means for removing duplicate rows from the query result are not duplicated. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
307 Duplicate rules Find multiple rules with the same definition (event, condition, action) on the same table. 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 system catalog base tables only 2025-11-07 10:11 MIT License View
308 Duplicate specification of character classes Find regular expressions where within the same specification of a character class the character class alnum as well as 0-9, \d, A-Z, or a-z has been defined. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
309 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
310 Duplicate triggers Find cases where the same table has multiple triggers with the same type (row-level, statement-level) that react to the same event with the same WHEN condition and with the same way (by invoking the same function). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
311 Duplicate user-defined routines Find user-defined routines with the exact duplicate body and parameters. There should not be multiple routines with exactly the same body and parameters (name, type). Having such duplicates is redundancy. Do remember that the same task can usually be solved in multiple different ways. Thus, the exact copies of routine bodies are not the only possible duplication. Moreover, it could be that different routines that solve the same task have different parameter names (but the parameters have the same types, ordinal positions, default values or the routines have different order of parameters). Thus, the query does not find all the duplications. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
312 Duplicate views Find views with exactly the same subquery. There should not be multiple views with exactly the same subquery. 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
313 Duplication of case insensitivity specification in a regular expression Find regular expressions that use both case insensitive search operator ~* and case insensitivity modifier (?i). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
314 Duplication of parent table CHECK constraints on the foreign key columns Find duplicate constraints, which make it more difficult to maintain the constraints. 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+system catalog base tables 2025-11-07 10:11 MIT License View
315 Duplication of simple CHECK constraints on the same column Find duplication of simple CHECK constraints on the same base table or foreign table column. Duplication of the same constraint means that if one starts to manage the code, then changes have to be made in multiple places. The problem is essentially similar with the data redundancy problem that database normalization tries to reduce. 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+system catalog base tables 2025-11-07 10:11 MIT License View
316 Each table is both referencing and referenced table (perhaps there is a cycle in relationships) Find as to whether the relationships between tables form a complete bidirected graph. Tables are vertices in the graph. There is a directed edge between two vertices if one of the tables refers to another through foreign key relationship on mandatory columns. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
317 Empty columns Find columns in non-empty tables that do not contain any values. If there are no values in a columns, then it may mean that one hasn't tested constraints that have been declared to the column or implemented by using triggers. It could also mean that such columns are not needed at all. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
318 Empty schemas This query identifies empty schemas within the database. A schema is considered empty if it exists as a namespace but contains no database objects, such as tables, views, functions, or types. The presence of such schemas often indicates artifacts from failed or incomplete migrations, obsolete application components, or setup errors, and they can be safely removed to reduce schema clutter. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-13 14:15 MIT License View
319 Empty tables Find base tables where the number of rows is zero. If there are no rows in a table, then it may mean that one hasn't tested constraints that have been declared to the table or implemented by using triggers. It could also mean that the table is not needed because there is no data that should be registered in the table. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
320 Enumerated or range types with the same name in different schemas This query enforces the Don't Repeat Yorself principle across the database's type system. It identifies ENUM and RANGE types that share the same name but exist in different schemas. This indicates that a conceptual data type has been defined multiple times instead of having a single, canonical definition in a shared schema. Such duplication leads to maintenance overhead and the risk of semantic divergence over time. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 12:38 MIT License View