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
101 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
102 Columns for registration and update times Find base table columns that based on the names and data types are meant for registering registration time or update time. Make sure that the columns have the same properties. General INFORMATION_SCHEMA only 2024-12-18 11:47 MIT License View
103 Sequence generators not needed Find possible classifier tables that have a column with a sequence generator. Such tables should have natural keys instead of surrogate keys. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-18 10:00 MIT License View
104 Unnecessary use of gist index type in case of an exclude constraint Find exclude constraints that are based on the gist index type although the default b-tree index type would have been enough. Problem detection system catalog base tables only 2024-12-16 13:45 MIT License View
105 ROW level BEFORE triggers that do not return a row if a check succeeds Find ROW level BEFORE triggers that check a condition based on other rows, raise an exception but do not return the row if the condition check succeeds, i.e., exception is not raised. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-16 12:41 MIT License View
106 Some data modification functions return a value and some not Find as to whether there are data modification routines that return a value as well as data modification routines that do not return a value. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-16 00:02 MIT License View
107 Base table columns with the type VARCHAR(1) The choice of data types should reveal as much as possible about the nature of the data in the column. The type of these columns could be CHAR(1) and they should have a constraint that a value in the column cannot be an empty string. Problem detection INFORMATION_SCHEMA only 2024-12-14 13:41 MIT License View
108 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 2024-12-14 13:41 MIT License View
109 Number of views with and without security barrier Find the number of views, the number of views with and without security barrier setting, and the names of views with and without the security barrier setting. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2024-12-13 19:41 MIT License View
110 Views without security barrier Find views that do not have the security barrier option. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-13 19:30 MIT License View
111 Views with WHERE but without security barrier Find views that do not have the security barrier option but restrict rows in some way. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-13 19:29 MIT License View
112 Explicit locking is missing PostgreSQL uses multiversion concurrency control (MVCC). SELECTs do not block modifications and vice versa. One has to take steps to achieve correct behaviour. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-13 14:57 MIT License View
113 SELECT * in a routine body SELECT statement should list the columns not use SELECT * to return data from all the columns. Firstly, it ensures, that the query asks only data that is really needed by the routine. It means less data that the DBMS has to fetch and pass to the routine. It could also mean that the DBMS can answer to a query based on an index without reading table blocks. Secondly, it documents the data that is returned by the query. The query does not consider objects that are a part of an extension. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-13 14:34 MIT License View
114 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 2024-12-12 09:42 MIT License View
115 Perhaps inconsistent use of temporal functions (2) Find as to whether in the same database more than one of these functions is used at the same time - now(), localtimestamp, current_timestamp. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-12 00:20 MIT License View
116 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 2024-12-11 23:29 MIT License View
117 FILLFACTOR is probably too small (2) Find base tables that probably implement many to many relationship type, have no clear sign that there are columns that should be updated, but still have fillfactor less than 100. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-11 14:38 MIT License View
118 B-tree index fillfactor has been explicitly set to 90 Find B-tree indexes that fillfactor has been explicitly set to 90. In case of B-tree indexes the default is 90. Problem detection system catalog base tables only 2024-12-11 14:37 MIT License View
119 Something is still to do in routines Find routines where comments contain TODO phrase. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-11 12:44 MIT License View
120 Row level triggers that update or delete data Find row level triggers that update or delete data. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-11 12:27 MIT License View