PostgreSQL Query Catalog

Find roles that a member can grant to others, i.e., the role has been granted with ADMIN OPTION. The number of privileges that can be passed on should be as small as possible.

Find routine privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible.

Find table privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible.

Find usage privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible.

Find foreign key columns that name contains twice the name of the referenced (primary) table.

This query identifies base table columns that unnecessarily include the table name. It searches for columns that are not part of a primary or foreign key and contain the name of their parent table. To avoid flagging legitimate naming conventions, it explicitly excludes a list of generic column names (e.g., name, description, nimi, kommentaar) where prefixing with the table name is considered good practice for improving clarity in queries.

This query enforces a concise coding style by checking the names of parameters within routines (such as functions or procedures). It finds parameters whose names unnecessarily repeat the name of the routine they belong to. For example, in a function named calculate_invoice, a parameter named calculate_invoice_id would be flagged, as invoice_id is sufficient. A routine cannot have two parameters with the same name, so the shorter name is unambiguous within the context of the routine and results in cleaner, more readable code.

This query identifies schema objects with names that are redundantly prefixed with their own schema's name. It flags any object whose name begins with the schema name plus at least one other character. This enforces the design principle that a schema is a sufficient namespace, and therefore, objects within it do not require the additional, repetitive context in their own names.

Find indexes that are identical, i.e., have the same properties, including uniqueness. The query considers all types of indexes, including indexes that have been automatically created to support a constraint and function-based indexes.

Find identifiers that explicitly say that they carry no meaning. Such identifier is called "unnamed" or "anonymous".

This query identifies identity columns defined with the GENERATED BY DEFAULT clause. Unlike GENERATED ALWAYS, this configuration permits manual insertion of values into the identity column without explicit overrides. This flexibility creates a significant risk of sequence desynchronization: if a user manually inserts a key value that exceeds the current sequence state, the sequence will eventually generate a colliding value. This results in runtime unique constraint violations (primary key conflicts) that are difficult to predict and resolve. The preferred pattern for surrogate keys is typically GENERATED ALWAYS to enforce system-controlled uniqueness.

This query identifies base table columns designated for storing codes (e.g., vin and isbn codes) that lack appropriate length constraints reflecting real-world data requirements. It operates on a heuristic basis, targeting columns whose identifiers imply code data (e.g., names containing "isbn" or "vin") but whose definitions fail to account for standard maximum lengths. This includes both insufficient allocation (truncation risk) and unbounded allocation (data quality risk). Ensuring these fields are sized according to domain standards is crucial for data integrity and usability.

Find columns of base tables that based on the default value of the column contain database username. However, the type of the column is not VARCHAR(63) or VARCHAR(128).

This query identifies base table columns designated for storing address components (e.g., IP addresses, emails, telephone numbers, or physical locations) that have inappropriate length constraints. It flags columns where the defined field size is either too strict or too loose compared to real-world data requirements. Operating on a heuristic basis, the query targets columns whose names imply address data (e.g., containing 'addr' or 'mail') but whose definitions fail to align with standard lengths. Highlighting both insufficient allocation (truncation risk) and unbounded allocation (data quality risk) helps ensure that these fields are sized according to domain standards, which is crucial for data integrity and usability.

This query identifies a semantic mismatch between the name and implementation of CHECK constraints (on tables, foreign tables, or domains). It targets constraints whose names suggest they validate against whitespace-only strings (e.g., names containing 'whitespace', 'space', 'blank'), but whose logic inappropriately uses the trim() function. The trim() function is a formatting tool for removing leading/trailing spaces, not a validation tool for ensuring a string is not composed entirely of whitespace. This indicates a likely implementation error, as a more robust regular expression (e.g., column !~ '^\s*$') is the correct tool for this type of validation.

Find base table columns that name refers to the possibility that these are used to register dates. Find the columns that do not have an appropriate data type. Column names should reflect the data that is possible to record in the column. For instance, in case of temporal data the column name should indicate as to whether we record dates or timestamps. If the column data type is "date", then the suffix of the column name should be "kp" (Estonian) or "date" (English).

Find base table columns that name refers to the possibility that these are used to register timestamps. Find the columns that do not have an appropriate data type. Column names should reflect the data that is possible to record in the column. For instance, in case of temporal data the column name should indicate as to whether we record dates or timestamps. If the column type is "timestamp", then the suffix of the column name should be "aeg" (Estonian) or "time" (English).

Find table columns with timestamp/date types that data type and dynamically found default value have a different type.

Find table columns with time types, which data type and dynamically found default value have a different type.

Naming of foreign key and referenced candidate key columns should be consistent. It cannot be so that in one table a value is labeled "id" like some surrogate key value and in another it "turns" into human-usable "code" or vice versa. An example:

Person(person_id, name)
Primary Key (person_id)

E_mail_address(e_mail_address_id, person_code, address)
Primary Key (e_mail_address_id)
Foreign key (person_code) References Person (person_id)