PostgreSQL Query Catalog

Find expressions that use a regular expression with a like predicate pattern.

Find expressions that use LIKE (including ILIKE) predicate with a regular expression patterns. In a LIKE pattern one can use only _ and % metasymbols to construct a pattern.

Find expressions that use LIKE predicate for precise comparison.

Find expressions that use LIKE predicate witout a pattern that contains at least one % or _ sign.

Find expressions where a date literal with the ISO format is converted to date/timestamp by using to_date or to_timestamp function.

Find extensions that routines are in the schema public.

Find for different types of database objects all the prefixes that are used in different names. One should be consistent in naming, including in the use of prefixes. If you use prefix in the name of a database object, then it could refer to the type of the database object. Do not use different prefixes in the names of database objects that have the same type.

Find for each base table the last time when the table has been vacuumed or analyzed (either manually or automatically).

Find for each base table the list of derived tables (both views and materialized views) that refer to the base table. If the database is used through the public database interface (virtual data layer), then, ideally, each table is referred from the subquery of at least one derived table.

Find for each base table the list of routines (functions and procedures) that refer to the base table. If the database is used through the public database interface (virtual data layer), then, ideally, each table is referred from at least one routine.

Find for each derived table the list of base tables that are used by the derived table.

Find for each domain the number of usages in base tables. The less you have managed to reuse domains in case of different columns, the more you have wasted your time by creating the domains.

Find for each user schema that contain a SQL-language routine how many of these have the body that is string literal and how many have the body (possible starting from PostgreSQL 14) that is parsed at the routine definition time. Try to be consistent, i.e., use the same solution in case of all the routines.

Find for each view or materialized view the number of tables based on which the derived table has been directly defined. These tables could be base tables or derived tables.

Find foreign data wrappers that do not have any associated foreign servers.

Find foreign key columns that are associated with a sequence generator. Foreign key values are selected amongst the values that are registered as corresponding primary key/unique key values. Values in the foreign key columns are not directly generated by the system. These values might be system generated indirectly - generated when a row is added to the primary (parent) table.

Find foreign key columns that are covered by a simple check constraint, i.e., the constraint involves only one column. Look only constraints that are directly associated with the table, i.e., are not specified through a domain. Perhaps the constraint should be defined on the referenced candidate key column.

Find foreign key columns that do not have an index. Foreign key columns are often used for performing join operations. It is useful to index such columns.

Find foreign key columns that do not have smallint, integer, bigint, or varchar(n) type. These are the most commonly used types in case of key/foreign key columns. Although the use of other types would be perfectly legal as well, make sure that you have selected the best possible data type for each and every column.

Find foreign key columns that do not have the NOT NULL constraint. It is better to limit the use of NULLs and optional columns due to the problems that it causes in interpreting the query results, making queries, and enforcing constraints. In addition, one should check as to whether this kind of design is consistent with the multiplicities in the conceptual data model.