Catalog of PostgreSQL queries for finding information about a PostgreSQL database and its design problems

AND
AND
ANDQueries of this category provide information about temporal (time-related) data that is kept in the database.
ANDFrom where does the query gets its information?
AND
AND

There are 25 queries.

Seq nrNameGoalTypeData sourceLast updateLicense...
1Base table columns permitting temporal values that may be outside the range of logical valuesFind base tables columns with temporal types (date and timestamp) that do not belong to a foreign key and that do not have any associated simple CHECK constraints, i.e., constraint that involves only one column. For instance, in the column registration_time that does not have any associated CHECK constraints could be values '1200-01-01 00:00' or '5900-12-31 00:00'. Rows with these values most probably represent wrong propositions and the system should restrict registration of such data.Problem detectionINFORMATION_SCHEMA+system catalog base tables2023-12-17 00:40MIT License
2Base tables where certainly registration time is not recordedFind base tables that do not have any column with a timestamp type. In such tables certainly registration time is not recorded. Make sure as to whether recording registration time is necessary.GeneralINFORMATION_SCHEMA only2021-02-26 00:41MIT License
3Base tables with multiple temporal columnsFind base tables that have more than one column with a temporal type (date or timestamp).GeneralINFORMATION_SCHEMA only2021-02-19 17:37MIT License
4Cannot accommodate all the fractional seconds in case of table columnsThe 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 detectionINFORMATION_SCHEMA only2021-02-25 17:30MIT License
5CHECK constraints on columns with temporal dataIf 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.GeneralINFORMATION_SCHEMA only2023-12-25 12:37MIT License
6CHECK constraints that use non-deterministic functionsDiscover 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.GeneralINFORMATION_SCHEMA only2020-11-06 14:51MIT License
7Columns for registration and update timesFind 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.GeneralINFORMATION_SCHEMA only2024-12-18 11:47MIT License
8Different ways how to find default timestamp valuesFind 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 detectionINFORMATION_SCHEMA only2023-12-08 16:08MIT License
9Do you really need fractional seconds?Find default values that return current timestamp with the maximum number of fractional seconds (6).Problem detectionINFORMATION_SCHEMA only2021-02-25 17:29MIT License
10Inconsistency between the name and the type of a base table column (dates)Find base table columns that name refers to the possibility that these are used to register dates. Find the columns that do not have an appropriate data type. Column names should reflect the data that is possible to record in the column. For instance, in case of temporal data the column name should indicate as to whether we record dates or timestamps. If the column data type is "date", then the suffix of the column name should be "kp" (Estonian) or "date" (English).Problem detectionINFORMATION_SCHEMA only2021-03-27 16:16MIT License
11Inconsistency between the name and the type of a base table column (timestamps)Find base table columns that name refers to the possibility that these are used to register timestamps. Find the columns that do not have an appropriate data type. Column names should reflect the data that is possible to record in the column. For instance, in case of temporal data the column name should indicate as to whether we record dates or timestamps. If the column type is "timestamp", then the suffix of the column name should be "aeg" (Estonian) or "time" (English).Problem detectionINFORMATION_SCHEMA only2021-03-28 15:12MIT License
12Inconsistency between the type and the default value of a column (date and timestamp values)Find table columns with timestamp/date types that data type and dynamically found default value have a different type.Problem detectionINFORMATION_SCHEMA only2021-02-25 17:29MIT License
13Inconsistent time zone and precision usage in case of registering timesFind as to whether different data types (with and without timezone) and precisions are used in case of registering times in different columns.Problem detectionINFORMATION_SCHEMA only2023-11-04 12:30MIT License
14Inconsistent time zone and precision usage in case of registering timestampsFind as to whether different data types (with and without timezone) and precisions are used in case of registering timestamps in different columns.Problem detectionINFORMATION_SCHEMA only2023-11-04 12:28MIT License
15Incorrect use of non-deterministic functions in CHECK constraintsDo not create a constraint in case of which data that satisfied a constraint c at the registration time suddenly does not satisfy it any more as the time goes by. Find all the check constraints that use non-deterministic functions (now, current_timestamp, localtimestamp, current_date, current_time) in a way that makes this situation possible. Fort instance, localtimestamp(0)>end_date is an example of such constraint.Problem detectionINFORMATION_SCHEMA only2024-11-22 15:29MIT License
16Perhaps a CHECK constraint about the order of events is missingFind 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 detectionINFORMATION_SCHEMA+system catalog base tables2021-02-25 17:29MIT License
17Perhaps a state machine is implemented with timestamp columnsFind implementations of state machines that uses a set of columns with a timestamp type.Problem detectionINFORMATION_SCHEMA only2021-03-26 21:04MIT License
18Perhaps incorrect check of permitted timestamp valuesFind base table columns with a timestamp type and a simple check constraint that checks as to whether the timestamp values are within an appropriate range of values. Find constraints where the check of the upper bound of the range is perhaps incorrect.Problem detectionINFORMATION_SCHEMA+system catalog base tables2023-11-09 10:27MIT License
19Perhaps the type of a base table column/domain should be temporal (based on default values)Find base table columns and domains that have a textual type but the default value that represents a temporal value (either a static value or invocation of a function that returns such value). Specify for each column/domain a right data type that takes into account expected values in the column/domain.Problem detectionINFORMATION_SCHEMA only2021-02-25 17:30MIT License
20Perhaps the type of a base table column/domain should be temporal (deadlines)Find base table columns that name refers to the possibility that there are registered deadlines but the column does not have a temporal type.Problem detectionINFORMATION_SCHEMA only2021-03-21 17:00MIT License