PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1040 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
41 Base tables with multiple temporal columns Find base tables that have more than one column with a temporal type (date or timestamp). General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
42 Boolean column for gender Find base table columns that have Boolean type and based on the column name are meant for recording data about gender. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
43 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 2025-11-07 10:11 MIT License View
44 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 2025-11-07 10:11 MIT License View
45 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 2025-11-07 10:11 MIT License View
46 CHECK constraints on columns with Boolean data Find check constraints that involve columns with the type Boolean. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
47 CHECK constraints on columns with personal names Find CHECK constraints on base table or foreign table columns that contain data about personal names. Make sure that the constraints do not restrict registration of legal names. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
48 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 2025-11-07 10:11 MIT License View
49 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 2025-12-14 12:48 MIT License View
50 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 2025-11-19 16:31 MIT License View
51 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 2025-11-07 10:11 MIT License View
52 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 2025-11-07 10:11 MIT License View
53 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 2025-11-07 10:11 MIT License View
54 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 2025-11-07 10:11 MIT License View
55 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 2025-11-07 10:11 MIT License View
56 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 2025-11-07 12:30 MIT License 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 2025-11-07 10:11 MIT License 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 2025-11-07 10:11 MIT License 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 2025-11-07 10:11 MIT License 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 2025-11-07 10:11 MIT License View