PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1040 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
461 Is does not return a boolean This query identifies user-defined SQL and PL/pgSQL routines (excluding triggers) that exhibit a semantic inconsistency between their name and return type. It flags non-trigger routines whose names begin with a predicate prefix (e.g., is_, has_, can_, on_) but whose defined return type is not BOOLEAN. Such a mismatch violates the principle of self-documenting code, as the name creates a strong expectation of a TRUE/FALSE return value, which the routine's signature then contradicts. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-13 14:03 MIT License View
462 IS NOT DISTINCT FROM in derived tables Avoid using IS NOT DISTINCT FROM because it makes the query planner to avoid using an index. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
463 IS NOT DISTINCT FROM in routines Avoid using IS NOT DISTINCT FROM because it makes the query planner to avoid using an index. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
464 LEAKPROOF routines that are perhaps not leakproof You should not give wrong information to the database management system. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
465 Length and char_length functions are used within the same expression This query identifies expressions that use both the length() and char_length() functions, which is a strong indicator of a logical flaw. Since these functions are functional synonyms in PostgreSQL (both returning the character count), an expression that compares their results (e.g., length(col) > char_length(col)) may be either tautological or will always evaluate to false. Such code is typically written under the mistaken assumption that length() returns bytes, a behavior seen in other database management systems. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-13 13:12 MIT License View
466 Lifecycle not initiated Find non-primary key and non-unique base table foreign key columns that name refers to the possibility that these are used to register references to a state classifier. The column must belong to a foreign key and does not have a default value. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
467 LIKE instead of = Find expressions that use LIKE predicate for precise comparison. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
468 LIKE with a regular expression pattern Find expressions that use LIKE (including ILIKE) predicate with a regular expression patterns. In a LIKE pattern one can use only _ and % metasymbols to construct a pattern. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
469 LIKE without a pattern with % or _ Find expressions that use LIKE predicate witout a pattern that contains at least one % or _ sign. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
470 Logical size of a schema The schema size is the sum of the sizes of all tables in the schema. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
471 Logical size of a table The table size is the sum of the total size of the simple columns and the total size of the complex columns in the table. In case of SQL databases large base tables in terms of number of columns could be a side effect of the problems with cloned columns or multiple columns for the same attribute. A base table with a low normalization level, which is meant to hold data that corresponds to multiple entity types has typically also relatively large number of columns compared with other base tables. Thus, the normalization level of base tables with a large number of columns should be checked as well. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
472 Mandatory columns for holding large textual values (comments, descriptions, etc.) Find mandatory (NOT NULL) base table columns that name, column type, and field size refers to the possibility that these are used to register large textual values like comments, descriptions, and explanations. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
473 Mandatory non-primary key columns Find mandatory non-primary key columns, i.e., the columns that have NOT NULL constraint. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
474 Many-to-many relationship types that do not have additional attributes Find base tables that implement many-to-many relationship types that do not permit repeating relationships. More specifically find tables that have two or more foreign keys and all the columns of the table are either foreign key columns or a surrogate key column. It could be that during the system analysis a mistake has been made and some attributes of the entity type that represents the relationship type have not been discovered. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
475 Many-to-many relationship types that do not permit repeating relationships and do not have additional attributes Find base tables that implement many-to-many relationship types that do not permit repeating relationships. More specifically find tables that have two or more foreign keys and all the columns of the table belong to a foreign key. In addition, all the table columns must be covered by a primary key or unique constraint. It could be that during the system analysis a mistake has been made and some attributes of the entity type that represents the relationship type have not been discovered. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
476 Meaningless terms in derived tables Find derived tables that subquery contains terms "foo", "bar", "foobar", or "baz". Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
477 Meaningless terms in names Find names of database objects that contain terms "foo", "bar", "foobar", or "baz". Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
478 Meaningless terms in routines Find routines that subquery contains terms "foo", "bar", "foobar", or "baz". Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
479 Median and average number of subcomponents in the names of database objects "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) Names should be expressive. Find the median and average (arithmetic mean) number of subcomponents in the names of user-defined database objects. The values could be used to compare different databases. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
480 Median and average of the length of names of database objects "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) Names should be expressive. Find the median length and average (arithmetic mean) length of the names (identifiers) of user-defined database objects. The values could be used to compare different databases. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View