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
321 Meaningless terms in derived tables Find derived tables that subquery contains terms "foo", "bar", "foobar", or "baz". Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
322 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
323 Derived tables with multiple DISTINCT's Find derived tables (views and materialized views) that contain more than one DISTINCT invocation. Make sure that the query is correctly written, including that it does not have unwanted formation of a Cartesian product. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
324 Derived table presents the same data in the same way as a single base table Find derived tables (views and materialized views) that present data from one base table without adding columns, renaming columns, changing the order of columns, removing columns, or restricting rows. Perhaps something is missing from the subquery of the derived table. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
325 Sorting rows based on random values in derived tables without limiting rows Find derived tables (views and materialized views) that sort rows based on random values but do not limit the number of rows. This is unnecessary because without sorting the rows are returned in a unspecified order. Sorting based on random values is a computationally expensive operation. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
326 Sorting rows based on random values in derived tables Find derived tables (views and materialized views) that sort rows based on random values. This can be used to find a random subset of rows. It is a computationally expensive operation. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
327 Set operations that do not remove duplicate rows in derived tables Find derived tables (views and materialized views) that use a set theoretic operation (union, except or intersect) in a manner that does not remove duplicate rows and thus can produce a multiset not a set. Make sure that it is what is needed. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
328 Too wide derived (dependent) table Find derived tables (views, materialized views) that are based on more than five tables and that have more than 15 columns. This view might produce "a denormalized world view" where all the data is together in one table and applications make queries based on this single view to fulfill their specific tasks. Such view does not follow the separation of concerns principle. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
329 Derived tables with sorting Find derived tables where the rows are ordered, i.e., there is ORDER BY clause at the end of the view. Different users may want to see the rows in different order. Thus, the DBMS may have to do extra and unnecessary work by firstly sorting based on one set of rows and after that based on other set of rows. Use sorting if you know that all the users want the rows in the same order and cannot/will not change the order in the application. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
330 Perhaps searching based on a name instead of a code Find derived tables with a search condition that is possible based on a name instead of a code. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
331 Deterministic (immutable) functions that do not have input parameters Find deterministic functions that do not have any input parameters. Make sure that it is correct because in general a deterministic function must calculate a value based on input. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
332 Deterministic (immutable) functions that do not return a value Find deterministic (immutable) functions that do not return a value. This goes against the idea of deterministic functions. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
333 Different tasks of rules Find different tasks that are solved by using rules, i.e., different rules on the same table or different tables that do the same thing are considered to solve one task. General system catalog base tables only 2025-11-07 10:11 MIT License View
334 Different tasks of triggers Find different tasks that are solved by using triggers, i.e., different triggers on the same table or different tables that do the same thing are considered to solve one task. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
335 Domain CHECK constraints with the same name Find domain check constraint names that are used more than once (within the same schema or in different schemas). Different things should have different names. However, here different constraints have the same name. Also make sure that this is not a sign of duplication of domains. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
336 Too generic names (domain constraints) Find domain CHECK constraints that have a too generic name - for instance, the name contains word "data" ) or the name is an abbreviation of the constraint type name (for instance, "chk" or "chk1"). Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
337 Unused domains (for base table columns and parameters) Find domains that are not used in case of any base table column and routine (input or otput) parameter (as their type). Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
338 Domain declares the same default value for multiple independent foreign keys Find domains that declare a default value and that are used in case of multiple foreign key constraints that point to different tables. Domains should be used in a manner that does not cause unnecessary coupling of concerns. For instance, let us assume that columns client_state_type_code of table Client (that is used to implement the relationship with table Client_state_type) and worker_state_type_code of table Worker (that is used to implement the relationship with table Worker_state_type) have been defined based on the same domain. It the domain has a default value, then it determines the initial state of both clients and workers. However, it must be possible to determine the initial state independently in case of clients and workers. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
339 Domain based on another domain Find domains that have been defined based on another domain. Do not specify domains based on existing domains. This would unnecessarily increase dependencies and complexity. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
340 Default should be declared at the level of domain not at the level of base table columns Find domains that have been used to define one or more base table non-foreign key columns and all the columns have the same default value that is associated directly with the column not with the domain. Write as little code as possible. If possible, move things "before the brackets" so to say. In this case it means declaring the default value at the level of the domain and not at the level of base table columns. An exception is when the domain is used to define foreign key columns. In this case, it would be appropriate to define the default value at the column level (because different foreign keys could have different default values). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View