PostgreSQL Query Catalog

Find the names of domain CHECK constraints that have been system-generated. Names should follow the same style. If there is a mix of system-generated and user-defined names, then the style is most probably different.

Find the names of database constraints that have been system-generated. Additional restrictions are that the constraints must involve only one column and are associated directly with a table (not through a domain). Names should follow the same style. If there is a mix of system-generated and user-defined names, then the style is most probably different.

Find CHECK constraints of base table and foreign table columns that are not valid. These constraints have been created so that the existing data has not been checked against the constraint. It could be deliberate in case of legacy systems that have data quality problems. However, ideally all the data in the table conforms to the constraint.

Find base table and foreign table constraint names that are used in a database more than once (possibly in different schemas or in case of different types of constraints). Different things should have different names. But here different constraints have the same name. Also make sure that this is not a sign of duplication.

Find table functions that use OFFSET. OFFSET method is a common way for implementing pagination.

You should follow the principle of least privilege and thus not have in your database tables that usage privileges are granted to the pseudo-role PUBLIC, i.e., to all the database users now and in the future.

Find table, routine, and usage privileges that have been granted to a superuser. Superuser can do anything in the database and thus does not need the privileges. The result is a sign that perhaps the executed GRANT statements were incorrect (wrong username) or the grantee later got superuser status (that it shouldn't have).

Do not have in a database elements that are not useful. PostgreSQL permits tables with no columns. Such tables can be used to implement Boolean variables (tables TABLE_DEE and TABLE_DUM). On the other hand, such tables might be a result of database evolution, where developers have not noticed that they have dropped all the columns of a table or have not noticed that they have created such a table in the first place.

Find base table columns with a check constraint that refers to a temporal function (current_timestamp, localtimestamp, current_date, or now) that return type is inconsistent with the data type of the column.

This query identifies semantic validation gaps in textual columns intended to store structured codes. It targets non-foreign key columns whose identifiers imply a specific format (e.g., containing the word "code"), but which lack adequate constraints to enforce that format. Specifically, it flags columns that have either no CHECK constraints at all, or only trivial constraints that prohibit empty/whitespace strings. Since "codes" typically adhere to a strict pattern (e.g., fixed length, specific character set), relying solely on a non-empty check is considered insufficient for data integrity.

Find indexed textual columns where the indexing does not consider the possibility of pattern-based search. Such columns do not have an index where the used operator class makes the index suitable for use by queries involving pattern matching expressions.

Write correct code. If you pass arguments to a trigger function, then the function should use the arguments. TG_ARGV[]: "Data type array of text; the arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value." (PostgreSQL documentation)

Find check constraints of domains where the Boolean expression invokes an operation that does not match with the data type of the domain.

Find surrogate keys where the generator can output the same value more than once. Key values must be unique, i.e., at some point the generator will prevent adding new rows to the table.

Perhaps the character maximum length has been omitted accidentally, i.e., one wrote VARCHAR instead of VARCHAR(n) where n is the maximum permitted number of characters in the field value. VARCHAR and TEXT are synonyms.

Find user-defined non-trigger SQL and PL/pgSQL routines where the beginning of the name of the routine indicates a certain action inside the routine (INSERT, UPDATE, or DELETE) but there is no such statement in the routine body.

This query audits the metadata of user-defined routines to enforce traceability between the implementation and the design specifications. It identifies routines whose comments lack a standardized reference to the specific database operation contract they implement. The query checks for the absence of a required identifier pattern, typically formatted as OP followed by a number (e.g., OP1, OP12). Enforcing this standard ensures that every routine can be mapped back to its originating requirement or business rule.

Using PL/pgSQL may cause context switching between declarative SQL and procedural PL/pgSQL. Thus use PL/pgSQL only if you truly need some of its constructs (variables, conditional statements, cycles, cursors, exception handling).

Using PL/pgSQL may cause context switching between declarative SQL and procedural PL/pgSQL. Thus use PL/pgSQL only if you truly need some of its constructs. You can create table functions by using SQL.

Find the names of table CHECK constraints that have the same Boolean expression but a different naming style in different places (tables). The naming of constraints should be consistent. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code)x