PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 9 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
1 Case insensitive search Refers to the column pg_proc.prokind and thus works starting from PostgreSQL 11. Find user-defined routines and derived tables (views/materialized views) that have a subquery with case insensitive search (by using the upper or lower function or ILIKE predicate or (?i) modifier of a regular expression pattern). General INFORMATION_SCHEMA+system catalog base tables 2021-10-25 16:55 MIT License View
2 Case sensitive and case insensitive uniqueness of the same simple key Find sets of columns that have both case sensitive and case insesitive unique constraints enforced based on these columns. In case of textual columns uniqueness could be checked either in case sensitive or case insensitive way. It is a contradiction to check the uniqueneness in both ways in case of the same key. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
3 Database objects of the same type and case insensitive name in the same container Find database objects with the same type and case insensitive name in the same container. It can only happen if some of the names are case insensitive and others are case sensitive. For instance, the same schema can contain the table "Client" and client Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-21 17:13 MIT License View
4 Delimited identifiers Delimited identifiers (quoted identifiers) are case sensitive. Identifiers of database objects should be case insensitive in order to simplify their management. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-21 17:21 MIT License View
5 Duplication of case insensitivity specification in a regular expression Find regular expressions that use both case insensitive search operator ~* and case insensitivity modifier (?i). Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-04 12:49 MIT License View
6 Function Upper or Lower is used in an index on a non-textual column Find function-based indexes that are based on function Upper or Lower but have been defined on a non-textual column. Such indexes support case insensitive search but in case of non-textual columns this does not have a meaning. Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View
7 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 2024-12-21 17:27 MIT License View
8 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-05-05 07:07 MIT License View
9 Partial or case insensitive unique indexes Find partial or case insensitive unique indexes. These implement uniqueness constraints that are impossible to enforce with the help of SQL's regular UNIQUE constraint. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View