PostgreSQL Query Catalog

Code should be as compact as possible and system should make as little work as necessary in order to solve a task. Thus, instead of selecting different fields of the same row with different SELECT INTO statements one should try to do it with one statement.

This query identifies base tables with a potentially redundant key structure, specifically those having more than one PRIMARY KEY or UNIQUE constraint defined on a single integer-type column. This pattern may suggest the presence of multiple surrogate keys for the same entity, which can indicate overcomplicated data model. A single entity should typically have only one system-generated identifier to maintain schema clarity, simplify join logic, and avoid redundancy. The presence of multiple such keys warrants a review to determine if one is superfluous.

Find base tables that have multiple triggers to update tsvector values.

Code should be as compact as possible and system should make as little work as necessary in order to solve a task. Thus, instead of updating different fields of the same table with different UPDATE statements one should try to do it with one statement.

This query identifies foreign key relationships that are established using name columns rather than standard identifiers. In a well-designed database, relationships should be built upon stable natural keys (such as codes) or system-generated surrogate keys, rather than mutable textual names.

Find names of database objects that contain only consonants and digits.

Find names of database objects that do not contain any vowels.

Find regular expressions where the names of character classes are not completely in lowercase. For instance, incorrect is to write [[:UPPER:]] or [[:Upper:]] and correct is [[:upper:]].

Find columns of tables (base tables, views, materialized views, foreign tables) that have the name first_name or last_name. Such column names do not take into account that different cultures use different personal name components and the number of possible components is more than two. If in a culture, the surname is presented before the given name, then the column names causes confusion.

Find constraints that are perhaps badly named. Find names of constraints (directly connected to a base table) and non-unique indexes that do not contain the associated column name.

Find constraints that are perhaps badly named. Table names help us to ensure the uniqueness of the names within a schema and make the names more expressive and user-friendly.

Find names (identifiers) of user-defined database objects that are regular identifiers and contain the $ sign starting from the second position. "Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable." (PostgreSQL manual)

Full uppercase means screaming and it makes comprehending the names more difficult. Find the names (identifiers) of user-defined database objects that are fully uppercase. Because PostgreSQL stores regular identifiers lowercase in the system catalog it also means that these are delimited identifiers, i.e., these are case sensitive.

Find names of database objects that contain a dollar sign ($) that is not the first symbol of the name. In PostgreSQL regular identifiers cannot start with $. However, $ can be used in other positions of the name. "Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable." (PostgreSQL documentation)

Find names of database objects that end with a underscore.

Use consistent style of naming. Prefer snake_case. Regular identifiers are stored in the PostgreSQL system catalog in lowercase. Thus, if you use, for instance the identifier thisIsLongTableName, then, for instance,in the pg_dump result you will see the table name thisislongtablename. If the name in the system catalog is thisIsLongTableName, then it means that the name is a delimited identifier, i.e., case sensitive.

This query identifies user-defined database objects that share a common container and base name, where the identifiers are distinguished solely by numerical suffixes (e.g., columns address1, address2). To avoid false positives—such as domains like d_name_50 and d_name_100 where the number signifies a length—the query employs a specific heuristic. It assumes a sequence starts with 1, 2, and 3. By removing these numbers from object names, it checks if multiple objects of the same type and base name result within the same container. A positive match strongly implies an intentional, sequential numbering. This pattern indicates a denormalized design, which complicates querying and is difficult to scale. The correct approach is to normalize the schema by creating a separate table for the repeating attribute.

This query identifies user-defined database object identifiers that terminate in one or two digits. This naming pattern is a design smell, as it often indicates either a bad database structure or the use of "magic numbers" that obscure the identifier's semantic meaning. Database object names should be fully descriptive and self-documenting. The presence of a numerical suffix necessitates a review to determine if a more descriptive name is required (e.g., renaming report_23 to report_for_year_2023) or if the data model needs to be changed.

This query identifies user-defined database objects that share a common container and a common base name, where the identifiers are distinguished solely by numerical suffixes (e.g., columns address1, address2 in the same table or tables address1 and address2 in the same schema). Such a structure complicates querying (e.g., requiring checks across multiple columns or tables) and is difficult to scale. The correct approach is for example to create a separate table for the repeating attribute, establishing a one-to-many relationship with the parent table.

Find names of database objects with four or more consecutive identical symbols