PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1041 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
581 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
582 Names of database objects that end with an underscore Find names of database objects that end with a underscore. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
583 Names of database objects that mix snake_case and camelCase/PascalCase Use consistent style of naming. Prefer snake_case. Regular identifiers are stored in the PostgreSQL system catalog in lowercase. Thus, if you use, for instance the identifier thisIsLongTableName, then, for instance,in the pg_dump result you will see the table name thisislongtablename. If the name in the system catalog is thisIsLongTableName, then it means that the name is a delimited identifier, i.e., case sensitive. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
584 Names of database objects that perhaps end with a sequence number This query identifies user-defined database objects that share a common container and base name, where the identifiers are distinguished solely by numerical suffixes (e.g., columns address1, address2). To avoid false positives—such as domains like d_name_50 and d_name_100 where the number signifies a length—the query employs a specific heuristic. It assumes a sequence starts with 1, 2, and 3. By removing these numbers from object names, it checks if multiple objects of the same type and base name result within the same container. A positive match strongly implies an intentional, sequential numbering. This pattern indicates a denormalized design, which complicates querying and is difficult to scale. The correct approach is to normalize the schema by creating a separate table for the repeating attribute. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-17 13:14 MIT License View
585 Names of database objects that perhaps end with a sequence number (2) This query identifies user-defined database object identifiers that terminate in one or two digits. This naming pattern is a design smell, as it often indicates either a bad database structure or the use of "magic numbers" that obscure the identifier's semantic meaning. Database object names should be fully descriptive and self-documenting. The presence of a numerical suffix necessitates a review to determine if a more descriptive name is required (e.g., renaming report_23 to report_for_year_2023) or if the data model needs to be changed. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-17 13:17 MIT License View
586 Names of database objects that perhaps end with a sequence number (3) This query identifies user-defined database objects that share a common container and a common base name, where the identifiers are distinguished solely by numerical suffixes (e.g., columns address1, address2 in the same table or tables address1 and address2 in the same schema). Such a structure complicates querying (e.g., requiring checks across multiple columns or tables) and is difficult to scale. The correct approach is for example to create a separate table for the repeating attribute, establishing a one-to-many relationship with the parent table. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-17 13:20 MIT License View
587 Names of database objects with four or more consecutive identical symbols Find names of database objects with four or more consecutive identical symbols Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
588 Names of database objects with perhaps too many digits Find the names of database objects where more than half the signs are digits. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
589 Names of database objects with perhaps too many subcomponents (terms) "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 number of subcomponents (terms) should not be too big. Find the names (identifiers) of user-defined database objects that perhaps contain too many subcomponents, assuming, that the separator of the components is "_". Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
590 Names of database objects with perhaps too many subcomponents (terms) that consist of only consonants Find names of database objects where the number of subcomponents (terms) that consist of only consonants is bigger than the number of subcomponents that contain at least one vowel. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
591 Names of indexes that do not contain the table name Find indexes that do not support a declarative constraint and that are perhaps badly named. Table names make the names more expressive and user-friendly. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
592 Names of the columns of derived tables that have been given by the system Find columns of derived tables that name has been given by the system. The creators of the table should specify the name themselves to avoid ugly names and nasty surprises. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
593 Names of triggers and rules that do not contain the table name The names should contain table name in order to make the names better understandable. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
594 Name starts or ends with spaces Find the names of user-defined database objects (must be delimited identifiers) that start or end with spaces. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
595 No need to use to_date or to_timestamp function Find expressions where a date literal with the ISO format is converted to date/timestamp by using to_date or to_timestamp function. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
596 Non-foreign key columns that have no associated CHECK constraints Find what are the base table columns that are not foreign key columns and that have no associated CHECK constraints? Perhaps some CHECK constraints are missing. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
597 Non-foreign key indexes that have been named as foreign key indexes Find indexes that are not on a foreign key column but the name of the index contains ixfk or idxfk. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
598 Non-key and non-foreign key base table columns with the same name and type that have in some cases permit NULLs and in some cases not Find non-key and non-foreign key base table columns with the same name and type that in some cases permit NULLs and in some cases not. Be consistent. Make sure that this selection is consistent. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
599 Non-predefined character classes must not be between double square brackets Write correct regular expressions. For instance, if there is a rule that code must consist of one or more digits, then correct expression is code~'^[0-9]+$', not code~'^[[0-9]]+$'. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
600 Non-updatable views that have data modification privileges Be precise and do not give impossible privileges. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View