Filter Queries

Found 1077 queries.

Page 3 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.

#41. Boolean state columns missing default values

INFORMATION_SCHEMA only

This query identifies boolean columns in base tables that lack a default value, but whose names suggest they implement a state machine or act as state flags. In good database design, such boolean state indicators should typically have a default value (false or true) to ensure a predictable initial state when new records are created.

Problem detection License: MIT (opens in new tab)

#42. Cannot accommodate all the fractional seconds in case of table columns

INFORMATION_SCHEMA only

The precision of a timestamp type of a column must be able to accommodate all the fractional seconds of the default value of the column. Find table columns with the type timestamp without time zone(m) or timestamp with time zone(m) that have a default value LOCALTIMESTAMP(n) or CURRENT_TIMESTAMP(n) WHERE n>m.

Problem detection License: MIT (opens in new tab)

#43. CHAR columns have a default value that length is shorter from the character maximum length of the column

INFORMATION_SCHEMA only

Choose a suitable data type, field size, and default value. If the default value is shorter from the character maximum length, then spaces will be added to the end of the registered value.

Problem detection License: MIT (opens in new tab)

#44. CHAR or VARCHAR columns have a default value that length is longer from the character maximum length of the column

INFORMATION_SCHEMA only

Find table columns with CHAR or VARCHAR type that have a default value that length is longer from the character maximum length of the column. Choose a suitable data type, field size, and default value. If the value is longer, then it is impossible to register it in a field, i.e., it makes registration of data impossible (except if the excessive characters are spaces).

Problem detection License: MIT (opens in new tab)

#45. CHECK constraints on columns with Boolean data

INFORMATION_SCHEMA only

Find check constraints that involve columns with the type Boolean.

General License: MIT (opens in new tab)

#46. CHECK constraints on columns with personal names

INFORMATION_SCHEMA only

This query identifies CHECK constraints applied to base or foreign table columns that store personal names. It flags these constraints for review to ensure they are not overly strict. Many regular expressions or character limits fail to account for the complexity of real-world names (such as those containing apostrophes, hyphens, or diacritics), thereby inadvertently preventing the registration of valid legal names.

General License: MIT (opens in new tab)

#47. CHECK constraints on columns with temporal data

INFORMATION_SCHEMA only

If your table contains columns with temporal data, then it will be appropriate to restrict the range of possible values in these columns because some of the values that belong to the type might not be appropriate (for instance, imagine a client who was born in 1100-12-03 or a contract that was registered in 3890-12-12- 12:45). If your table contains multiple columns with temporal data that denote events, then the rule about the order of the events must be enforced, if possible.

General License: MIT (opens in new tab)

#48. CHECK constraints on TIMESTAMP and DATE columns that explicitly specify a time zone

INFORMATION_SCHEMA only

This query identifies semantic anomalies in CHECK constraints applied to columns defined as DATE or TIMESTAMP (without time zone). It flags constraints that incorporate time zone conversion logic (e.g., using AT TIME ZONE). Since these data types store "naive" values devoid of time zone offsets, attempting to apply time zone logic makes the constraint's outcome dependent on the current session or server configuration. This non-deterministic behavior is a design flaw, as data validity should be intrinsic and immutable, not dependent on the environment.

Problem detection License: MIT (opens in new tab)

#49. CHECK constraints on TIMESTAMPTZ and TSTZRANGE columns that do not explicitly specify a time zone

INFORMATION_SCHEMA only

This query identifies CHECK constraints on TIMESTAMPTZ and TSTZRANGE columns that use timestamp literals with fixed UTC offsets instead of named time zones. This practice is flagged as a design flaw because fixed offsets do not account for Daylight Saving Time (DST), leading to constraints on both single timestamps and range boundaries that are unable to correctly represent a local time zone's rules throughout the entire year.

Problem detection License: MIT (opens in new tab)

#50. CHECK constraints that use non-deterministic functions

INFORMATION_SCHEMA only

Discover incorrect usage of non-deterministic functions in CHECK constraints. Find base table columns and foreign table columns that have a CHECK constraint that refers to a non-deterministic function that returns current date/time/timestamp.

General License: MIT (opens in new tab)

#51. Columns for registration and update times

INFORMATION_SCHEMA only

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

#52. Columns of base tables that hold truth values but do not have a default value (Boolean columns)

INFORMATION_SCHEMA only

Find columns of base tables that have type BOOLEAN but do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. Often it should be possible to select one of these as the default value of a column that has BOOLEAN type.

Problem detection License: MIT (opens in new tab)

#53. Columns of base tables that hold truth values that do not have a default value although they could have it (Boolean columns)

INFORMATION_SCHEMA only

Find columns of base tables that have type BOOLEAN. Based on column names these implement a state machine or record agreements. At the same time the columns do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. It should be possible to select one of these as the default value of the column.

Problem detection License: MIT (opens in new tab)

#54. Columns of base tables with data about postal addresses, file addresses, or web addresses that have an incorrect data type

INFORMATION_SCHEMA only

Find base table columns that name refers to the possibility that these are used to register file/web addresses. Find the columns where the type refers to the possibility that values in the column are actual files.

Problem detection License: MIT (opens in new tab)

#55. Columns with a range type that require a better name

INFORMATION_SCHEMA only

This query identifies columns with a RANGE data type that violate naming conventions. It flags columns whose names do not semantically suggest a range or period, which can create ambiguity and lead to incorrect assumptions when writing queries.

Problem detection License: MIT (opens in new tab)

#56. Columns with conflicting nullability rules

INFORMATION_SCHEMA only

This query identifies a severe logical contradiction in the database schema. It finds situations where a column is strictly defined as NOT NULL (either directly or inherited via a domain), but is simultaneously subject to a CHECK constraint that explicitly requires the value to be missing (e.g., containing an IS NULL condition). This conflict—whether applied as a single-column or multi-column constraint—creates a logical impossibility, preventing any valid data insertion into the table. The query outputs the schema name, table name, column name, domain name (if applicable), the column's mandatory status, and the definition of the problematic CHECK constraint.

Problem detection License: MIT (opens in new tab)

#57. Columns with exact/floating numeric types have textual default values

INFORMATION_SCHEMA only

The default value of a column should belong to the type of the column. The system shouldn't conduct unnecessary type casts.

Problem detection License: MIT (opens in new tab)

#58. Consistency of CHECK constraint name and content

INFORMATION_SCHEMA only

Find all CHECK constraints that name contains a digit. Make sure that the name and the Boolean expression are consistent.

General License: MIT (opens in new tab)

#59. Consistency of using NOT NULL constraints on Boolean base table columns

INFORMATION_SCHEMA only

Find the number of mandatory and optional Boolean base table columns and the proportion of optional columns from all the Boolean columns. Use two-valued logic (TRUE, FALSE) instead of three-valued logic (TRUE, FALSE, UNKNOWN). Because NULL in a Boolean column means unknown make all the Boolean columns mandatory.

Sofware measure License: MIT (opens in new tab)

#60. Data type usage in the base table columns

INFORMATION_SCHEMA only

Get overview of used data types in the columns of base tables. If the selection is very small then this is a warning sign that perhaps unsuitable types have been used.

General License: MIT (opens in new tab)
# Name(activate to sort ascending) Goal(activate to sort ascending) Type(activate to sort ascending) Data source(sorted ascending, activate to sort descending) Last update(activate to sort ascending) License Actions
41 Boolean state columns missing default values This query identifies boolean columns in base tables that lack a default value, but whose names suggest they implement a state machine or act as state flags. In good database design, such boolean state indicators should typically have a default value (false or true) to ensure a predictable initial state when new records are created. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
42 Cannot accommodate all the fractional seconds in case of table columns The precision of a timestamp type of a column must be able to accommodate all the fractional seconds of the default value of the column. Find table columns with the type timestamp without time zone(m) or timestamp with time zone(m) that have a default value LOCALTIMESTAMP(n) or CURRENT_TIMESTAMP(n) WHERE n>m. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
43 CHAR columns have a default value that length is shorter from the character maximum length of the column Choose a suitable data type, field size, and default value. If the default value is shorter from the character maximum length, then spaces will be added to the end of the registered value. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
44 CHAR or VARCHAR columns have a default value that length is longer from the character maximum length of the column Find table columns with CHAR or VARCHAR type that have a default value that length is longer from the character maximum length of the column. Choose a suitable data type, field size, and default value. If the value is longer, then it is impossible to register it in a field, i.e., it makes registration of data impossible (except if the excessive characters are spaces). Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
45 CHECK constraints on columns with Boolean data Find check constraints that involve columns with the type Boolean. General INFORMATION_SCHEMA only MIT (opens in new tab) View
46 CHECK constraints on columns with personal names This query identifies CHECK constraints applied to base or foreign table columns that store personal names. It flags these constraints for review to ensure they are not overly strict. Many regular expressions or character limits fail to account for the complexity of real-world names (such as those containing apostrophes, hyphens, or diacritics), thereby inadvertently preventing the registration of valid legal names. General INFORMATION_SCHEMA only MIT (opens in new tab) View
47 CHECK constraints on columns with temporal data If your table contains columns with temporal data, then it will be appropriate to restrict the range of possible values in these columns because some of the values that belong to the type might not be appropriate (for instance, imagine a client who was born in 1100-12-03 or a contract that was registered in 3890-12-12- 12:45). If your table contains multiple columns with temporal data that denote events, then the rule about the order of the events must be enforced, if possible. General INFORMATION_SCHEMA only MIT (opens in new tab) View
48 CHECK constraints on TIMESTAMP and DATE columns that explicitly specify a time zone This query identifies semantic anomalies in CHECK constraints applied to columns defined as DATE or TIMESTAMP (without time zone). It flags constraints that incorporate time zone conversion logic (e.g., using AT TIME ZONE). Since these data types store "naive" values devoid of time zone offsets, attempting to apply time zone logic makes the constraint's outcome dependent on the current session or server configuration. This non-deterministic behavior is a design flaw, as data validity should be intrinsic and immutable, not dependent on the environment. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
49 CHECK constraints on TIMESTAMPTZ and TSTZRANGE columns that do not explicitly specify a time zone This query identifies CHECK constraints on TIMESTAMPTZ and TSTZRANGE columns that use timestamp literals with fixed UTC offsets instead of named time zones. This practice is flagged as a design flaw because fixed offsets do not account for Daylight Saving Time (DST), leading to constraints on both single timestamps and range boundaries that are unable to correctly represent a local time zone's rules throughout the entire year. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
50 CHECK constraints that use non-deterministic functions Discover incorrect usage of non-deterministic functions in CHECK constraints. Find base table columns and foreign table columns that have a CHECK constraint that refers to a non-deterministic function that returns current date/time/timestamp. General INFORMATION_SCHEMA only MIT (opens in new tab) View
51 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 MIT (opens in new tab) View
52 Columns of base tables that hold truth values but do not have a default value (Boolean columns) Find columns of base tables that have type BOOLEAN but do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. Often it should be possible to select one of these as the default value of a column that has BOOLEAN type. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
53 Columns of base tables that hold truth values that do not have a default value although they could have it (Boolean columns) Find columns of base tables that have type BOOLEAN. Based on column names these implement a state machine or record agreements. At the same time the columns do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. It should be possible to select one of these as the default value of the column. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
54 Columns of base tables with data about postal addresses, file addresses, or web addresses that have an incorrect data type Find base table columns that name refers to the possibility that these are used to register file/web addresses. Find the columns where the type refers to the possibility that values in the column are actual files. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
55 Columns with a range type that require a better name This query identifies columns with a RANGE data type that violate naming conventions. It flags columns whose names do not semantically suggest a range or period, which can create ambiguity and lead to incorrect assumptions when writing queries. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
56 Columns with conflicting nullability rules This query identifies a severe logical contradiction in the database schema. It finds situations where a column is strictly defined as NOT NULL (either directly or inherited via a domain), but is simultaneously subject to a CHECK constraint that explicitly requires the value to be missing (e.g., containing an IS NULL condition). This conflict—whether applied as a single-column or multi-column constraint—creates a logical impossibility, preventing any valid data insertion into the table. The query outputs the schema name, table name, column name, domain name (if applicable), the column's mandatory status, and the definition of the problematic CHECK constraint. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
57 Columns with exact/floating numeric types have textual default values The default value of a column should belong to the type of the column. The system shouldn't conduct unnecessary type casts. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
58 Consistency of CHECK constraint name and content Find all CHECK constraints that name contains a digit. Make sure that the name and the Boolean expression are consistent. General INFORMATION_SCHEMA only MIT (opens in new tab) View
59 Consistency of using NOT NULL constraints on Boolean base table columns Find the number of mandatory and optional Boolean base table columns and the proportion of optional columns from all the Boolean columns. Use two-valued logic (TRUE, FALSE) instead of three-valued logic (TRUE, FALSE, UNKNOWN). Because NULL in a Boolean column means unknown make all the Boolean columns mandatory. Sofware measure INFORMATION_SCHEMA only MIT (opens in new tab) View
60 Data type usage in the base table columns Get overview of used data types in the columns of base tables. If the selection is very small then this is a warning sign that perhaps unsuitable types have been used. General INFORMATION_SCHEMA only MIT (opens in new tab) View