PostgreSQL Query Catalog

Domains 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.

Domains 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.

Find 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).

Find cases where you store images and other media as files outside the database and store in the database only paths to the files.

Find cases where you store images and other media as files outside the database and store in the database only paths to the files.

"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).

Find cases where a base table has been split horizontally into multiple smaller base tables based on the distinct values in one of the columns of the original table. Each such newly created table has the name, a part of which is a data value from the original tables. Find base tables that have the same columns (column name, column order, data type) and the difference between the tables are the numbers in the table names (table1, table2, etc.).

Find base tables that implement recording multivalued attribute values with the help of repeating group of columns. Find base tables that have more than one columns with the same type and field size and the difference between the columns are the numbers in the column names (column1, column2, etc.).

Avoid creating user-defined routines that have the same name as some extension routine because it may cause confusion.

Find, based on column names, cases where a multi-valued attribute in a conceptual data model is implemented as a textual column of a base table or a foreign table. Expected values in the column are strings that contain attribute values, separated by commas or other separation characters.

Find, based on default values, cases where a multi-valued attribute in a conceptual data model is implemented as a textual column of a base table or a foreign table. Expected values in the column are strings that contain attribute values, separated by commas or other separation characters.

Find, based on the data that users have recoreded in a database, cases where a multi-valued attribute in a conceptual data model is implemented as a textual column of a base table. Expected values in the column are strings that contain attribute values, separated by commas or other separation characters.

Try to find missing foreign key constraints. Find columns of base tables that are not covered by any foreign key constraint but belong to a composite key, do not have an associated sequence generator, and have a name that refers to the possibility that these are used to record some kind of codes or id's. Moreover, there must be at least one other base table that has a column with the same name. Such strategy would find missing constraints in tables that implement many-to-many relationship types but which that are not complete "islands" in terms of missing foreign key constraints.

Try to find missing foreign key constraints. Find non-key and non-foreign key columns of base tables that do not have an associated sequence generator, and that name refers to the possibility that the column holds parent identifiers.

This query identifies short text columns in base tables that are not part of any primary or foreign key, but whose names closely match an existing table in the database. This pattern suggests that the similarly named table might be a classifier (reference) table, and the flagged column should ideally have a foreign key constraint referencing it.

Try to find missing foreign key constraints. Find columns of base tables that are not a part of any primary key, unique, and foreign key constraint, do not have an associated sequence generator, but have a name that reffers to the possibility that these are used to record some kind of codes or id's.

Try to find missing foreign key constraints. Find columns of base tables that are not a part of any primary key, unique, and foreign key constraint, but have a name that reffers to the possibility that these are used to record references to a user. Exclude columns that have the default value CURRENT_USER or SESSION_USER.

Try to find missing foreign key constraints. Find base tables that do not participate in any referential constraint (as the referenced table or as the referencing table). These tables are like "islands" in the database schema.

Try to find missing foreign key constraints. Find pairs of base table columns that have the similar name, perhaps the same type, and that are not associated through a foreign key relationship.

Find routines where in SELECT or UPDATE statements references to columns are prefixed with references to the table schema. Referring to schema in this context bloats the code.