Filter Queries

Found 1077 queries.

Page 35 of 54
  • 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 the relatively small amount of data in the system catalog of a database.

#681. Perhaps a CHECK constraint about the order of events is missing

INFORMATION_SCHEMA+system catalog base tables

Find base tables that have at least two columns that have DATE or TIMESTAMP (with or without time zone) type and do not have any associated CHECK constraint that involves two or more of these columns. The columns mean that we want to record data about events or processes, which often have a certain order. Hence, in case of each row of such a table the values in these columns must be in a certain order. For instance, the end of a meeting cannot be earlier than the beginning of the meeting.

Problem detection License: MIT (opens in new tab)

#682. Perhaps an existing domain could be used to define the properties of a base table column?

INFORMATION_SCHEMA+system catalog base tables

Find non-foreifgn key base table columns that have not been defined based on a domain but that have the same properties (data type, field size, default value, and pemisson to use NULLs) as some domain. If you define a domain, then you should try to use it in case of multiple columns.

Problem detection License: MIT (opens in new tab)

#683. Perhaps an inconsistent use of NO ACTION and RESTRICT in the foreign key declarations

system catalog base tables only

Find as to whether in case of foreign key constraints both the compensating actions RESTRICT and NO ACTION are used within the same database. If the same thing has to do in different places, then try to do it in the same way.

Problem detection License: MIT (opens in new tab)

#684. Perhaps an overcomplicated constraint expression that compares the result of a Boolean expression with a Boolean value

INFORMATION_SCHEMA+system catalog base tables

Find table and domain CHECK constraints that compare the result of a Boolean expression with a Boolean value. If you can choose between two logically equivalent Boolean expressions choose the more simple expression.

Problem detection License: MIT (opens in new tab)

#685. Perhaps an unnecessary default value (the empty string or a string that consists of only whitespace) of a base table column/domain

INFORMATION_SCHEMA only

This query identifies table columns and domains that are configured with a semantically void DEFAULT value. It specifically flags defaults that are an empty string ('') or a string consisting solely of whitespace characters (e.g., spaces, newlines). This practice is a design flaw because it automatically populates the database with non-substantive data, which can lead to application-level bugs when code does not explicitly check for such "blank" values in addition to NULL.

Problem detection License: MIT (opens in new tab)

#686. Perhaps an unsuitable use of CHAR(n) type in base tables (based on check constraints)

INFORMATION_SCHEMA+system catalog base tables

This query identifies a logical redundancy and likely data type misuse by finding CHAR columns that have a CHECK constraint on their value's length. The CHAR(n) data type is fixed-width and space-padded, meaning any non-NULL value will have a character length of exactly n. Therefore, a CHECK constraint on length is either superfluous (if it checks <= n) or will always fail (if it checks < n). This pattern indicates that the developer intended to store a variable-length string with a maximum length, for which the VARCHAR data type is the correct and more efficient choice.

Problem detection License: MIT (opens in new tab)

#687. Perhaps an unsuitable use of CHAR(n) type in base tables (based on names)

INFORMATION_SCHEMA+system catalog base tables

This query identifies the semantic misuse of the CHAR(n) data type for non-foreign key columns where n > 1. It operates on a heuristic, flagging columns whose names suggest they store variable-length data (e.g., "name", "comment", "description", "email") rather than genuinely fixed-length data like standardized codes or hash values. Because CHAR(n) is a fixed-width, space-padded type, its use for variable-length strings is inefficient in terms of storage and can introduce application-level logic errors, making VARCHAR(n) the appropriate alternative.

Problem detection License: MIT (opens in new tab)

#688. Perhaps a redundant column (based on sequence generators)

INFORMATION_SCHEMA only

Find base tables where more than one column gets the default value by using the sequence generator mechanism.

Problem detection License: MIT (opens in new tab)

#689. Perhaps a reference to the variable NEW is missing

INFORMATION_SCHEMA+system catalog base tables

This query identifies row-level BEFORE INSERT and BEFORE UPDATE triggers whose primary purpose is not to raise an exception, but which never reference the NEW record outside of the RETURN clause. In PostgreSQL, BEFORE triggers are specifically intended either to validate incoming data (raising exceptions upon failure) or to manipulate column values by modifying NEW. If a trigger does neither, it typically indicates that its logic (such as logging or updating related tables) should be relocated to an AFTER trigger, or that the trigger contains obsolete, redundant code.

Problem detection License: MIT (opens in new tab)

#690. Perhaps a reference to the variable OLD is missing

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#691. Perhaps a regular expression could be simplified

INFORMATION_SCHEMA+system catalog base tables

Find regular expressions that name character classes a-zA-Z.

Problem detection License: MIT (opens in new tab)

#692. Perhaps a relationship should be irreflexive

system catalog base tables only

Enforce all the constraints. A binary relation is called irreflexive, if it does not relate any element to itself.

Problem detection License: MIT (opens in new tab)

#693. Perhaps a state machine is implemented with Boolean columns

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#694. Perhaps a state machine is implemented with timestamp columns

INFORMATION_SCHEMA only

Find implementations of state machines that uses a set of columns with a timestamp type.

Problem detection License: MIT (opens in new tab)

#695. Perhaps a too generic foreign key column name

system catalog base tables only

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 License: MIT (opens in new tab)

#696. Perhaps a too long PL/pgSQL routine

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#697. Perhaps a too long SQL routine

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#698. Perhaps a too simplified state machine

INFORMATION_SCHEMA only

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 License: MIT (opens in new tab)

#699. Perhaps a unneccessary surrogate key

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#700. Perhaps brackets are missing in a regular expression that uses OR logical operation

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)
# Name(activate to sort ascending) Goal(activate to sort ascending) Type(sorted ascending, activate to sort descending) Data source(activate to sort ascending) Last update(activate to sort ascending) License Actions
681 Perhaps a CHECK constraint about the order of events is missing Find base tables that have at least two columns that have DATE or TIMESTAMP (with or without time zone) type and do not have any associated CHECK constraint that involves two or more of these columns. The columns mean that we want to record data about events or processes, which often have a certain order. Hence, in case of each row of such a table the values in these columns must be in a certain order. For instance, the end of a meeting cannot be earlier than the beginning of the meeting. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
682 Perhaps an existing domain could be used to define the properties of a base table column? Find non-foreifgn key base table columns that have not been defined based on a domain but that have the same properties (data type, field size, default value, and pemisson to use NULLs) as some domain. If you define a domain, then you should try to use it in case of multiple columns. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
683 Perhaps an inconsistent use of NO ACTION and RESTRICT in the foreign key declarations Find as to whether in case of foreign key constraints both the compensating actions RESTRICT and NO ACTION are used within the same database. If the same thing has to do in different places, then try to do it in the same way. Problem detection system catalog base tables only MIT (opens in new tab) View
684 Perhaps an overcomplicated constraint expression that compares the result of a Boolean expression with a Boolean value Find table and domain CHECK constraints that compare the result of a Boolean expression with a Boolean value. If you can choose between two logically equivalent Boolean expressions choose the more simple expression. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
685 Perhaps an unnecessary default value (the empty string or a string that consists of only whitespace) of a base table column/domain This query identifies table columns and domains that are configured with a semantically void DEFAULT value. It specifically flags defaults that are an empty string ('') or a string consisting solely of whitespace characters (e.g., spaces, newlines). This practice is a design flaw because it automatically populates the database with non-substantive data, which can lead to application-level bugs when code does not explicitly check for such "blank" values in addition to NULL. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
686 Perhaps an unsuitable use of CHAR(n) type in base tables (based on check constraints) This query identifies a logical redundancy and likely data type misuse by finding CHAR columns that have a CHECK constraint on their value's length. The CHAR(n) data type is fixed-width and space-padded, meaning any non-NULL value will have a character length of exactly n. Therefore, a CHECK constraint on length is either superfluous (if it checks <= n) or will always fail (if it checks < n). This pattern indicates that the developer intended to store a variable-length string with a maximum length, for which the VARCHAR data type is the correct and more efficient choice. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
687 Perhaps an unsuitable use of CHAR(n) type in base tables (based on names) This query identifies the semantic misuse of the CHAR(n) data type for non-foreign key columns where n > 1. It operates on a heuristic, flagging columns whose names suggest they store variable-length data (e.g., "name", "comment", "description", "email") rather than genuinely fixed-length data like standardized codes or hash values. Because CHAR(n) is a fixed-width, space-padded type, its use for variable-length strings is inefficient in terms of storage and can introduce application-level logic errors, making VARCHAR(n) the appropriate alternative. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
688 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 MIT (opens in new tab) View
689 Perhaps a reference to the variable NEW is missing This query identifies row-level BEFORE INSERT and BEFORE UPDATE triggers whose primary purpose is not to raise an exception, but which never reference the NEW record outside of the RETURN clause. In PostgreSQL, BEFORE triggers are specifically intended either to validate incoming data (raising exceptions upon failure) or to manipulate column values by modifying NEW. If a trigger does neither, it typically indicates that its logic (such as logging or updating related tables) should be relocated to an AFTER trigger, or that the trigger contains obsolete, redundant code. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
690 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 MIT (opens in new tab) View
691 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 MIT (opens in new tab) View
692 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 MIT (opens in new tab) View
693 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 MIT (opens in new tab) View
694 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 MIT (opens in new tab) View
695 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 MIT (opens in new tab) View
696 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 MIT (opens in new tab) View
697 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 MIT (opens in new tab) View
698 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 MIT (opens in new tab) View
699 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 MIT (opens in new tab) View
700 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 MIT (opens in new tab) View