PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 997 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
361 Do you really need fractional seconds? Find default values that return current timestamp with the maximum number of fractional seconds (6). Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View
362 Deferrable foreign key constraint with a RESTRICT compensating action Find deferrable foreign key constraint with ON UPDATE RESTRICT or ON DELETE RESTRICT compensating action. Referential actions are carried out before, and are not part of, the checking of a referential constraint. Deferring a referential constraint defers the checking of the of the constraint (a ) but does not defer the referential actions of the referential constraint. In PostgreSQL the essential difference between NO ACTION and RESTRICT is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not. Thus RESTRICT could result with the failure of data modification where in case of NO ACTION the modification would succeed. Problem detection system catalog base tables only 2021-10-08 11:29 MIT License View
363 Too generic names regarding persons and their names (columns of derived tables) Find derived table columns that name refers to the possibility that these contain person names. The names of derived table columns that contain person names should refer to the role that the corresponding entity type has in the view. For instance, if view active_product has a column with the name surname, then the column name does not give information what is the role of the person in the context of the view. Better name would be, for instance, registrator_surname. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-10 14:25 MIT License View
364 Invocation of PL/pgSQL functions from the subqueries of derived tables Find derived tables that subqueries invoke a PL/pgSQL function. Avoid context switch that is caused by the invocation of PL/pgSQL functions from the subqueries of derived tables. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-24 14:30 MIT License View
365 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 2023-01-06 14:08 MIT License View
366 FOR UPDATE in derived tables Find derived tables that subquery uses FOR UPDATE construct. Reading a data element shouldn't block other read operations of the same element. Thus, you shouldn't use exclusive locking command in a view. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-12-12 11:11 MIT License View
367 Perhaps USING syntax could be used for joining in the subqueries of derived tables Find derived tables that use newer join syntax where join conditions are written in the WHERE clause but do not use USING synatx. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-10 14:14 MIT License View
368 Perhaps Trim is missing Find derived tables (views and materialized views) that apparently concatenate values (by using || operator or use Concat function or use Format function) by putting a space between these but do not use Trim function to get rid of the extra space at the beginning or the end of the string. Problem detection system catalog base tables only 2023-12-13 13:53 MIT License View
369 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-01-15 14:16 MIT License View
370 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 2020-12-10 13:25 MIT License View
371 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 2023-12-25 12:15 MIT License View
372 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 2021-02-25 17:30 MIT License View
373 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 2021-02-25 17:29 MIT License View
374 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 2020-12-10 13:32 MIT License View
375 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 2022-12-13 12:34 MIT License View
376 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 2020-12-22 21:34 MIT License View
377 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 2024-12-18 11:52 MIT License View
378 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 2023-11-09 19:53 MIT License View
379 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 2023-12-20 19:33 MIT License View
380 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 2024-01-14 16:10 MIT License View