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
421 Inconsistency of using parameter data types Find parameters of routines that have the same name but a different type. Parameters that have the same name should have, in general, the same data type as well, assuming that the routines, which have the parameters, have different names, i.e., there is no overloading in play. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
422 Inconsistent CHECK constraints for same-named columns This query audits the schema for inconsistent data validation logic. It identifies non-foreign key base table columns that share the same identifier (name) across different tables but enforce a divergent set of single-column CHECK constraints. According to the "Clean Code" principle of consistency, a recurring attribute name (e.g., status_code) implies a shared domain concept and should be subject to identical validity rules globally. Discrepancies indicate that business rules are being applied unevenly, potentially compromising data integrity. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-05 19:02 MIT License View
423 Inconsistent CHECK constraints on columns with the same name This query identifies inconsistencies in data validation for columns that share the same name across different tables. It flags cases where a conceptual data element (e.g., 'email', 'postal_code') is subject to a CHECK constraint in some tables but lacks one in others. This violates the principle of uniform data integrity, creates semantic ambiguity, and can allow invalid data to enter the system through the unconstrained columns. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-15 09:53 MIT License View
424 Inconsistent digit character class syntax in regular expressions This query audits regular expressions within the database to detect syntactical inconsistencies in identifying numeric digits. It checks for the concurrent use of disparate character class notations: range-based ([0-9]), Perl-style shorthand (\d), and POSIX character classes ([[:digit:]]). While these are often functionally equivalent for standard ASCII digits, mixing multiple syntaxes within a single codebase indicates a lack of standardization, which reduces code readability and increases cognitive load during maintenance. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-25 17:13 MIT License View
425 Inconsistent names of database objects that are used to manage the state of main objects in the database "Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) The naming must be consistent. One should avoid mixing synonyms like "seisund", "staatus", and "olek" in Estonian or "state" and "status" in English and stick with one term. For instance, it is a bad practice to use word "state" in table names but word "status" in function names. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
426 Inconsistent naming of columns with BOOLEAN type This query audits the naming conventions of BOOLEAN columns to detect inconsistency. It checks whether the database contains a mix of styles: some boolean columns adhering to a semantic prefix convention (e.g., is_active, has_permission) and others using unprefixed nouns or adjectives (e.g., active, permission). The presence of both styles indicates a lack of a consistent standard, which can reduce schema clarity and predictability for developers. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-13 13:50 MIT License View
427 Inconsistent naming of comment columns Find columns of tables that start with the word comment or komment but end differently (excluding numbers). Return result only if there is more than one naming variant of such columns in the database. For instance, a column has the name "comment" but another "comments". Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
428 Inconsistent prefixing of generic column names This query validates column naming consistency, specifically targeting generic attributes such as comments or descriptions. It flags tables that exhibit a mixed convention: containing both prefixed (e.g., table_comment) and unprefixed (e.g., description) generic columns within the same database context. This excludes primary and foreign keys. The goal is to enforce a uniform style—either consistently prefixing generic columns with the table name or consistently omitting the prefix—rather than allowing a hybrid approach. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-05 09:44 MIT License View
429 Inconsistent prefixing of specific (non-generic) column names This query audits the database for inconsistent column naming conventions, specifically focusing on non-generic, domain-specific attributes (excluding primary keys, foreign keys, and generic fields like 'comment'). It detects a hybrid naming strategy: some columns are prefixed with the table name (e.g., employee_salary) and others are not (e.g., hiring_date). This inconsistency suggests a lack of a standardized data dictionary, making query writing unpredictable. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-05 10:15 MIT License View
430 Inconsistent referencing to character classes Find as to whether regular expressions use inconsistently references to character classes: [^\s], [^\d], [^\w], [^[:space:]], [^[:digit:]], [^[:word:]] vs [^\S], [^\D], [^\W]. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
431 Inconsistent regex character class syntax usage This query audits regular expressions across the database to detect inconsistent syntax when defining character classes. It specifically checks for the concurrent usage of Perl-style shorthand notation (e.g., \s, \d) and POSIX character classes (e.g., [[:space:]], [[:digit:]]). While often functionally overlapping, these syntaxes may have subtle behavioral differences depending on locale and engine versions. The presence of both styles within a single database indicates a lack of coding standards, reducing readability and increasing maintenance complexity. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-20 13:12 MIT License View
432 Inconsistent syntax for whitespace validation constraints This query audits the database schema for syntactical inconsistency in CHECK constraints designed to prohibit empty or whitespace-only strings. It identifies the concurrent use of disparate expression patterns—such as column ~ '\S', column !~ '^[[:space:]]*$', or trim(column) <> ''—to achieve the same functional validation. While these patterns may enforce identical logical rules, the lack of a single, standardized idiom increases cognitive load and maintenance complexity. The query facilitates a review to select one preferred syntax and enforce its uniform application across all relevant columns. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-19 15:00 MIT License View
433 Inconsistent usage of string concatenation methods within or accross different database objects This query audits the database schema to assess the consistency of string concatenation practices. It identifies the concurrent usage of multiple disparate methods for joining text: the standard concatenation operator (||) and the variadic functions concat() and concat_ws(). While each method has specific use cases (e.g., concat for null safety), indiscriminate mixing of these techniques across similar contexts indicates a lack of coding standards. The output facilitates a review to enforce a more uniform approach where appropriate. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-13 14:49 MIT License View
434 Inconsistent use of casting syntax in routines Find as to whether PL/pgSQL routines and SQL routines that do not have SQL-standard body use different syntax for casting (cast function vs :: operator). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
435 Inconsistent use of functions and procedures Find as to whether the database has both procedures as well as functions that do not return a value (i.e., return VOID). Such routines are generally used to modify data in the database. The support of procedures was added to PostgreSQL 11. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
436 Inconsistent use of gratuitous context in the names of non-foreign key and non-primary key columns This query validates column naming conventions. It returns a row only for tables that have an inconsistent mix of column naming styles—specifically, where some columns (that are not part of a primary or foreign key) are prefixed with the table name and others are not. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-05 10:12 MIT License View
437 Inconsistent use of length and char_length functions This query identifies inconsistent usage of string length functions within the database. Although length() and char_length() are functional synonyms in PostgreSQL (both returning the character count), mixing them violates clean coding principles. The query checks if both variants are present in the codebase, flagging a lack of standardization. Enforcing a single choice (typically the SQL-standard char_length or character_length) improves code maintainability and readability. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-13 12:56 MIT License View
438 Inconsistent use of older and newer join syntax in the subqueries of derived tables Find as to whether the subqueries of derived tables use both older join syntax (join condition in the where clause) and newer syntax (join condition in the from clause). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
439 Inconsistent use of plural and singular in column names in the context of a relationship (English version) Find foreign key constraints that cover one column in case of which the name of refererenced/referencing column is in plural and the name of referencing/refererenced column is in singular. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
440 Inconsistent use of plural and singular in table names in the context of a relationship (English version) Find foreign key constraints in case of which the name of one of the tables is in plural and the name of another table is in singular. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View