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

AND
AND
AND
ANDFrom where does the query gets its information?
AND
AND

There are 961 queries.

Seq nrNameGoalTypeData sourceLast updateLicense...
241Different 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
242Disabled rulesIdentify disabled rules. These should be enabled or dropped, otherwise these are dead code.Problem detectionsystem catalog base tables only2022-10-21 11:22MIT License
243Disabled system triggers (i.e., disabled enforcement of constraints)These triggers should be enabled because otherwise some important functionality regarding constraints like enforcing referential integrity does not work.Problem detectionsystem catalog base tables only2021-03-12 15:06MIT License
244Disabled user triggersIdentify disabled triggers. These should be enabled or dropped, otherwise these are dead code.Problem detectionsystem catalog base tables only2021-02-25 17:30MIT License
245Domain based on another domainFind domains that have been defined based on another domain. Do not specify domains based on existing domains. This would unnecessarily increase dependencies and complexity.Problem detectionINFORMATION_SCHEMA+system catalog base tables2021-02-25 17:29MIT License
246Domain candidatesFind column descriptions that are candidates for describing a domain.GeneralINFORMATION_SCHEMA+system catalog base tables2020-11-06 14:51MIT License
247Domain CHECK constraint name contains table nameFind names of domain CHECK constraints that contain the name of the base that column the domain specifies. Domains are reusable artifacts that one should be able to use in case of defining different base tables. Thus, it is inappropriate to use the name of a particular table in the name of a domain constraint.Problem detectionINFORMATION_SCHEMA only2021-02-25 17:29MIT License
248Domain CHECK constraints with the same nameFind domain check constraint names that are used more than once (within the same schema or in different schemas). Different things should have different names. However, here different constraints have the same name. Also make sure that this is not a sign of duplication of domains.Problem detectionINFORMATION_SCHEMA only2021-02-25 17:30MIT License
249Domain declares the same default value for multiple independent foreign keysFind domains that declare a default value and that are used in case of multiple foreign key constraints that point to different tables. Domains should be used in a manner that does not cause unnecessary coupling of concerns. For instance, let us assume that columns client_state_type_code of table Client (that is used to implement the relationship with table Client_state_type) and worker_state_type_code of table Worker (that is used to implement the relationship with table Worker_state_type) have been defined based on the same domain. It the domain has a default value, then it determines the initial state of both clients and workers. However, it must be possible to determine the initial state independently in case of clients and workers.Problem detectionINFORMATION_SCHEMA+system catalog base tables2021-02-25 17:29MIT License
250Domain name and type name are the sameUse different names to avoid confusion.Problem detectionINFORMATION_SCHEMA+system catalog base tables2021-02-25 17:30MIT License
251Domain name contains base table nameFind names of domains that contain the name of the table that column the domain specifies. Domains are reusable artifacts that one should be able to use in case of different base tables. Thus, it is inappropriate to use the name of a particular table in the name of the domain.Problem detectionINFORMATION_SCHEMA only2021-02-25 17:29MIT License
252Domains that are associated with a sequence generatorDomains are reusable artifacts. By associating a domain with a sequence generator, one essentially starts to share sequence generators between tables. It may cause a potential performance bottleneck by having a shared resource. By having a shared sequence it is not possible to change properties of sequences of different tables independently, i.e., it increases coupling between tables.Problem detectionINFORMATION_SCHEMA only2021-03-07 21:08MIT License
253Domains with the same name in different schemasDomains are like words that can be used to construct generalized claims about the real world (table predicates). Better not to duplicate the words in the dictionary.Problem detectionINFORMATION_SCHEMA only2021-02-25 17:30MIT License
254Domain usage in base tablesFind 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.GeneralINFORMATION_SCHEMA only2020-11-06 14:51MIT License
255Do not always depend on one's parentFind where a hierarchical structure is implemented in a base table by adding a foreign key that refers to a candidate key of the same table.Generalsystem catalog base tables only2021-03-12 15:36MIT License
256Do not always depend on one's parent - column names are ot sufficiently differentFind where a hierarchical structure is implemented in a base table by adding a foreign key that refers to a candidate key of the same table. Find only cases where the candidate key and foreign key column names are very similar (Levenshtein distance shorter than four).Problem detectionsystem catalog base tables only2022-11-26 17:19MIT License
257Do not always depend on one's parent (INFORMATION_SCHEMA)Find where a hierarchical structure is implemented in a base table by having a foreign key that refers to a candidate key of the same table. This design is called adjacency list.GeneralINFORMATION_SCHEMA only2021-03-07 10:57MIT License
258Do not assume you must use filesFind cases where you store images and other media as files outside the database and store in the database only paths to the files.Problem detectionINFORMATION_SCHEMA only2021-03-27 16:55MIT License
259Do not assume you must use files (based on user data)Find cases where you store images and other media as files outside the database and store in the database only paths to the files.Problem detectionINFORMATION_SCHEMA+system catalog base tables2021-03-10 12:58MIT License
260Do not clone columns"Split a base table column into multiple columns based on the values in some other column. Each such newly created column has the name, a part of which is a data value from the original tables."(Bill Karwin) Find base tables that have more than one columns with the same type and field size and the difference between the columns are the year or month number at the end of the column name (two or four numbers, preceded by an underscore).Problem detectionINFORMATION_SCHEMA only2022-11-28 15:15MIT License