PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1050 queries.

  • All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
  • Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets it shouldn't be a problem in case of relatively small amount of data, which is in the system catalog of a database.
  • Statistics about the catalog content and project home in GitHub that has additional information.

# Name Goal Type Data source ▲ Last update License
341 Domain candidates Find column descriptions that are candidates for describing a domain. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
342 Domain declares the same default value for multiple independent foreign keys Find 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 detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
343 Domain name and type name are the same Use different names to avoid confusion. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
344 Do 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 detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
345 Do not create user-defined routines that have the same name as some installed extension routine Avoid creating user-defined routines that have the same name as some extension routine because it may cause confusion. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
346 Do not format comma-separated lists (based on user data) 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. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
347 Do not leave out referential constraints (based on composite keys) 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. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
348 Do not leave out the referential constraints (based on adjacency list design) 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. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
349 Do not leave out the referential constraints (based on classifiers) 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. Problem detection INFORMATION_SCHEMA+system catalog base tables 2026-05-16 23:59 MIT License View
350 Do not leave out the referential constraints (based on column names) 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. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
351 Do not leave out the referential constraints (based on column names) (2) 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. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
352 Do not leave out the referential constraints (pairs of tables) 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. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
353 Do not refer to the table schema in the references to columns 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. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
354 Do not specify a list of values in a table column definition Find cases where the list of valid data values in the column is specified in the column definition (in addition to specifying the type of the column) by using, for instance, check constraints or enumerated types. The check constraint is either associated directly with a table or is associated with a domain. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
355 Do not use approach that one size fits all (primary key columns) Find base base tables have the simple primary key that contains the column with the (case insensitive) name id and an integer type. In addition, the primary key values are generated automatically by the system by using a sequence generator. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
356 Do not use approach that one size fits all (unique index columns) Find base base tables have a simple unique index (not associated with a constraint) that contains the column with the (case insensitive) name id and an integer type. In addition, the key values are generated automatically by the system by using a sequence generator. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
357 Double checking of the maximum character length This query identifies superfluous CHECK constraints where a programmatic length check duplicates a declarative, data type-based length limit. For instance, a CHECK constraint like char_length(column) <= 100 on a column already defined as VARCHAR(100) is redundant. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-13 13:14 MIT License View
358 Double negatives in Boolean expressions Write code that is simple to understand and not confusing. A double negative is a grammatical construction occurring when two forms of negation are used in the same expression (https://en.wikipedia.org/wiki/Double_negative). Double negatives in Boolean expressions make it more difficult to understand and maintain the code. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
359 Double negatives in regular expressions Fing regular expression patterns that use [^\S] instead of \s or [^\D] instead of \d or [^\W] instead of \w. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
360 Duplicate comments Find comments that have been registered with a COMMENT statement and that are associated with more than one object. It would probably mean that a comment is incorrect or missing. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View