PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1038 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
481 Median and average of the length of names of database objects by the object type "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 by the object type. 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
482 Middle-man Find a routine that's only task is to invoke another routine. If a routine performs only one action, delegating work to another routine, why does it exist at all? Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
483 Minimum tuple length required before trying to move long column values into TOAST tables has been changed Find base tables in case of which toast_tuple_target storage parameter value is not the default value (2040). "Changing this value may not be useful for very short or very long rows. Note that the default setting is often close to optimal, and it is possible that setting this parameter could have negative effects in some cases. " Make sure that the parameter has an optimal value. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
484 Missing USAGE privileges on schema If a user has a privilege to use a schema object, then the user must also have the usage privilege on the schema that contains the object. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
485 Multiple deletes from the same table in a routine Code should be as compact as possible and system should make as little work as necessary in order to solve a task. Thus, instead of deleting different rows of the same table with different DELETE statements one should try to do it with one statement. The query excludes routines where IF statement is used. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
486 Multiple RETURNING clauses in a SQL function Find SQL functions that have multiple statements with the RETURNING clause. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
487 Multiple selects from the same table in a routine Code should be as compact as possible and system should make as little work as necessary in order to solve a task. Thus, instead of selecting different fields of the same row with different SELECT INTO statements one should try to do it with one statement. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
488 Multiple simple keys with integer values This query identifies base tables with a potentially redundant key structure, specifically those having more than one PRIMARY KEY or UNIQUE constraint defined on a single integer-type column. This pattern may suggest the presence of multiple surrogate keys for the same entity, which can indicate overcomplicated data model. A single entity should typically have only one system-generated identifier to maintain schema clarity, simplify join logic, and avoid redundancy. The presence of multiple such keys warrants a review to determine if one is superfluous. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-15 11:35 MIT License View
489 Multiple updates of the same table in a routine Code should be as compact as possible and system should make as little work as necessary in order to solve a task. Thus, instead of updating different fields of the same table with different UPDATE statements one should try to do it with one statement. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
490 Name and description maximum length Find tables where is both a column for registering name and description. Find the permitted maximum field size in these columns. Take into account that the maximum length may be controlled by using a CHECK constraint. Make sure that the permitted maximum field sizes are sufficiently different. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
491 Name contains only consonants and digits Find names of database objects that contain only consonants and digits. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
492 Name does not contain any vowels Find names of database objects that do not contain any vowels. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
493 Name does not contain any vowels (aggregate view) Find aggregate information about the names of database objects that do not contain any vowels. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
494 Names of character classes are not in the lowercase Find regular expressions where the names of character classes are not completely in lowercase. For instance, incorrect is to write [[:UPPER:]] or [[:Upper:]] and correct is [[:upper:]]. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
495 Names of columns with the type BOOLEAN This query retrieves the names of all columns defined with the BOOLEAN data type to facilitate an audit of naming consistency. The primary objective is to verify adherence to a recommended best practice: boolean column names should be prefixed with a semantic predicate, such as is_ (in English) or on_ (in Estonian). This convention enhances the self-documenting nature of the schema and improves the readability of SQL statements by framing the column's purpose as a true/false question. General INFORMATION_SCHEMA+system catalog base tables 2025-11-13 14:08 MIT License View
496 Names of database objects (regular identifiers) that contain $ Find names (identifiers) of user-defined database objects that are regular identifiers and contain the $ sign starting from the second position. "Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable." (PostgreSQL manual) Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
497 Names of database objects that are fully uppercase Full uppercase means screaming and it makes comprehending the names more difficult. Find the names (identifiers) of user-defined database objects that are fully uppercase. Because PostgreSQL stores regular identifiers lowercase in the system catalog it also means that these are delimited identifiers, i.e., these are case sensitive. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
498 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. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
499 Names of database objects that contain a digit Find the names (identifiers) of user-defined database objects that contain at least one digit. Names should be informative. Duplicates should be avoided. Digits in names are a possible sign of duplication of database objects or unclear names. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
500 Names of database objects that contain dollar sign Find names of database objects that contain a dollar sign ($) that is not the first symbol of the name. In PostgreSQL regular identifiers cannot start with $. However, $ can be used in other positions of the name. "Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable." (PostgreSQL documentation) Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View