Filter 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 the relatively small amount of data in the system catalog of a database.
#41. Boolean state columns missing default values
INFORMATION_SCHEMA onlyThis 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.
#42. Cannot accommodate all the fractional seconds in case of table columns
INFORMATION_SCHEMA onlyThe 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.
#43. CHAR columns have a default value that length is shorter from the character maximum length of the column
INFORMATION_SCHEMA onlyChoose 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.
#44. CHAR or VARCHAR columns have a default value that length is longer from the character maximum length of the column
INFORMATION_SCHEMA onlyFind 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).
#45. CHECK constraints on columns with Boolean data
INFORMATION_SCHEMA onlyFind check constraints that involve columns with the type Boolean.
#46. CHECK constraints on columns with personal names
INFORMATION_SCHEMA onlyThis 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.
#47. CHECK constraints on columns with temporal data
INFORMATION_SCHEMA onlyIf 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.
#48. CHECK constraints on TIMESTAMP and DATE columns that explicitly specify a time zone
INFORMATION_SCHEMA onlyThis 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.
#49. CHECK constraints on TIMESTAMPTZ and TSTZRANGE columns that do not explicitly specify a time zone
INFORMATION_SCHEMA onlyThis 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.
#50. CHECK constraints that use non-deterministic functions
INFORMATION_SCHEMA onlyDiscover 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.
#51. Columns for registration and update times
INFORMATION_SCHEMA onlyFind 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.
#52. Columns of base tables that hold truth values but do not have a default value (Boolean columns)
INFORMATION_SCHEMA onlyFind 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.
#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 onlyFind 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.
#54. Columns of base tables with data about postal addresses, file addresses, or web addresses that have an incorrect data type
INFORMATION_SCHEMA onlyFind 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.
#55. Columns with a range type that require a better name
INFORMATION_SCHEMA onlyThis 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.
#56. Columns with conflicting nullability rules
INFORMATION_SCHEMA onlyThis 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.
#57. Columns with exact/floating numeric types have textual default values
INFORMATION_SCHEMA onlyThe default value of a column should belong to the type of the column. The system shouldn't conduct unnecessary type casts.
#58. Consistency of CHECK constraint name and content
INFORMATION_SCHEMA onlyFind all CHECK constraints that name contains a digit. Make sure that the name and the Boolean expression are consistent.
#59. Consistency of using NOT NULL constraints on Boolean base table columns
INFORMATION_SCHEMA onlyFind 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.
#60. Data type usage in the base table columns
INFORMATION_SCHEMA onlyGet 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.
| # | 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 |