PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1024 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
621 Perhaps a redundant column (based on sequence generators) Find base tables where more than one column gets the default value by using the sequence generator mechanism. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
622 Perhaps a reference to the variable NEW is missing Find row level before insert and before update triggers that only task is not to raise an exception and where the variable NEW is not used in the trigger routine outside the RETURN clause. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
623 Perhaps a reference to the variable OLD is missing Find row level before delete triggers that only task is not to raise an exception and where the variable OLD is not used in the trigger routine outside the RETURN clause. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
624 Perhaps a regular expression could be simplified Find regular expressions that name character classes a-zA-Z. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
625 Perhaps a relationship should be irreflexive Enforce all the constraints. A binary relation is called irreflexive, if it does not relate any element to itself. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
626 Perhaps a routine does not have a real task Find the routines where the only action is to return an argument value, a constant value, NULL or return the value of OLD or NEW variable in case of trigger functions. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
627 Perhaps a state machine is implemented with Boolean columns Find implementations of state machines that uses a set of one or more Boolean columns. These columns could have the type Boolean or could probably (based on the column name and non-participation in a foreign key) contain values that represent truth values. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
628 Perhaps a state machine is implemented with timestamp columns Find implementations of state machines that uses a set of columns with a timestamp type. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
629 Perhaps a too generic foreign key column name Find the names of foreign key columns that are too generic. The expressive names of table columns allow database users better and more quickly understand the meaning of data in the database. A person could participate in a process or be associated with an object due to different reasons. Thus, foreign key column names like isik_id, person_id, tootaja_id, worker_id etc. are too generic. The name should refer (also) to the reason why the person is connected. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
630 Perhaps a too long name, which has been automatically shortened Find names (identifiers) of user-defined database objects that are 63 bytes long. This is the longest permitted length of identifiers if the default value of the NAMEDATALEN parameter has not been changed. PostgreSQL shortens too long identifiers automatically. Automatic code modification could break it somewhere. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
631 Perhaps a too long PL/pgSQL routine A large routine may have multiple tasks that should be split between multiple routines, each of which has a more focused task. Find the PL/pgSQL routines where the number of physical lines of code is bigger than 40. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
632 Perhaps a too long SQL routine A large routine may have multiple tasks that should be split between multiple routines that have a more focused task. Find the SQL routines where the number of statements (logical lines of code) is bigger than 5. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
633 Perhaps a too simplified state machine Find base table columns with Boolean type that name refers to the possibility that these are used to register as to whether an entity is currently in active state or not. Find the base tables that have exactly one Boolean column. During the system design one should find all the possible states of an entity type that influence the behavior of the information system. Data as to whether an entity is in one of these states should be in the database. Having only two states - active/inactive - is sometimes a too big simplification. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
634 Perhaps a unneccessary surrogate key Find base tables that have the primary key that is not a surrogate key and an alternate key that is a surrogate key. Perhaps the surrogate key column is not needed. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
635 Perhaps brackets are missing in a regular expression that uses OR logical operation Find regular expressions where choice between alternatives has no brackets. Thus, instead of '(a|b|c)' there is 'a|b|c'. An example: '^a|b|c$' -permits in the string symbol "|" but '^(a|b|c)$' does not permit in the string symbol "|". Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
636 Perhaps character class [[:alnum:]] could be used Perhaps regular expressions that refer to character classes alnum and digit could be simplified. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
637 Perhaps check constraint names contain incorrect or unnecessary words Find names of check constraints (either associated with a base table or a domain) that names contain words that are not needed in the name. For instance, constraints cannot ensure the correctness of data and thus the word correct should not be used in the names. Words like "valid" or phrases like "follows_rules" are just noise because all the constraint ensure that the registered data values are valid and follow certain rules. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
638 Perhaps checking of file extension is incorrect Find check constraints of tables that use a regular expression to check as to whether a registered string ends with an appropriate file extension. However, the expression does not put the dot sign into the square brackets nor does have the escape character \before it, i.e., it is interpreted as a single character not as the dot sign in the expression. In regular expressions the dot (.) matches any single character except the newline character. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
639 Perhaps CHECK should be associated with a domain Find cases where multiple columns with the same domain have exactly the same CHECK constraint that is directly associated with the table. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
640 Perhaps Count(*) is wrongly used Find user-defined routines and derived tables (views/materialized views) that have a subquery that invokes Count aggregate function like this - Count(*), uses outer join, and grouping. In case of grouping you do not want to get an answer that an empty group contains one member. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View