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
241 User-defined derived tables Find user-defined views and materialized views. Pay attention to the outer join operations. One should use these if and only if there is a real need for them. Otherwise they just reduce performance. On the other hand, pay attention that outer join is used where it is logically needed. General INFORMATION_SCHEMA+system catalog base tables 2023-12-11 15:23 MIT License View
242 Explicit locking PostgreSQL uses Multi-version Concurrency Control (MVCC) and thus, sometimes, one has to explicitly lock certain rows or entire table. One has to use LOCK TABLE or SELECT … FOR UPDATE statements for that. General INFORMATION_SCHEMA+system catalog base tables 2023-12-11 15:02 MIT License View
243 Incorrect comparison operator Find PL/pgSQL routines that use comparison operators =< or =>. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-11 14:50 MIT License View
244 Perhaps inconsistent use of temporal functions Find routines that use temporal functions CURRENT_TIMESTAMP, LOCALTIMESTAMP, or now() that is inconsistent with the default values of the columns that are used by the routine, e.g., function uses a column with the default value LOCALTIMESTAMP but the routine uses function CURRENT_TIMESTAMP or now(). Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-10 17:11 MIT License View
245 Routine for reading data uses another routine to read some data Find routines that only read data but invoke some other routine to read some more data. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-10 17:10 MIT License View
246 Different ways how to find default timestamp values Find all the default values of base table, view, and foreign table columns that are expressions invoking a function that returns a timestamp. Do it only if there are different expressions, i.e., there could be possible inconsistencies. Problem detection INFORMATION_SCHEMA only 2023-12-08 16:08 MIT License View
247 ON DELETE CASCADE is not needed (based on classifier tables) Find foreign key constraints with ON DELETE CASCADE compensating action that refer to classifier (reference data) tables. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-08 14:52 MIT License View
248 All foreign key constraints Enforce referential integrity in database. Find all referential integrity (foreign key) constraints. General system catalog base tables only 2023-12-07 12:48 MIT License View
249 Explicit locking is missing (2) Find user-defined routines that have a subquery in a DELETE or UPDATE statement without the FOR UPDATE clause. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-06 12:15 MIT License View
250 Too generic names (parameters) (there is a parameter with a more specific name in the routine) Find parameter names in case of which the same routine has another parameter with the same mode but with more specific name, i.e., the name contains the parameter name in the end or in the beginning. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-06 12:03 MIT License View
251 Prefer Polymorphism to If/Else or Switch/Case (2) Find routines with multiple raise exception commands. Perhaps it has multiple tasks and it violates the separation of concerns and single responsibilities principles. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-29 13:39 MIT License View
252 Prefer Polymorphism to If/Else or Switch/Case Find routines with IF/ELSE or SWITCH/CASE statements. If your routine has a multipart IF/CASE statement, then perhaps it has multiple tasks and it violates the separation of concerns and single responsibilities principles. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-29 13:31 MIT License View
253 Updating or deleting data in a routine without restricting rows Find user-defined routines that contain UPDATE or DELETE statement but do not contain any WHERE clause, meaning that at least one UPDATE or DELETE operation influences all the rows of a table. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-29 13:21 MIT License View
254 Registration/modification time is not mandatory Find columns that contain registration or modification time but are optional. Problem detection INFORMATION_SCHEMA only 2023-11-26 16:51 MIT License View
255 Do not leave out the referential constraints (based on column names) Try to find missing foreign key constraints. Find columns of base tables that are not a part of any primary key, unique, and foreign key constraint, do not have an associated sequence generator, but have a name that reffers to the possibility that these are used to record some kind of codes or id's. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-26 16:35 MIT License View
256 Constraint-supporting UNIQUE indexes with the same leading column Find indexes that support a uniqueness constraint and have the same leading column. General system catalog base tables only 2023-11-26 16:01 MIT License View
257 Overlapping non-function based indexes that have the same leading column with the same operator class Find non-function based indexes (both unique and non-unique) that duplicate each other because their first column is identical and the operator class that is used in case of the first column is identical. Include unique indexes that support a constraint (primary key, unique, exclude), i.e., these indexes have been automatically created due to the constraint declaration. Problem detection system catalog base tables only 2023-11-26 15:59 MIT License View
258 Lifecycle not initiated Find non-primary key and non-unique base table foreign key columns that name refers to the possibility that these are used to register references to a state classifier. The column must belong to a foreign key and does not have a default value. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-26 15:40 MIT License View
259 A state machine is implemented with the help of an enumeration type Find implementations of state machines that uses an enumeration type. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-26 15:39 MIT License View
260 A state machine is implemented with the help of a state classifier table Find implementations of state machines that use a state classifier table. General system catalog base tables only 2023-11-26 15:39 MIT License View