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
641 The number of user-defined rules Find the number of rules in a database, excluding the rules that are associated with system catalog tables and rules that are created to support views. Rules can be used to maintain data integrity in a database by causing rejection of incorrect insertions and updates. Therefore, the number of rules in a database gives some indications about the state of enforcing constraints at the database level. Sofware measure system catalog base tables only 2025-11-07 10:11 MIT License View
642 The number of user-defined rules by schema and in total Find the number of rules in a database in different schemas, excluding the rules that are created to support views. Rules can be used to maintain data integrity in a database by causing rejection of incorrect insertions and updates. Therefore, the number of rules in a database gives an indication about the state of enforcing constraints at the database level. Sofware measure system catalog base tables only 2025-11-07 10:11 MIT License View
643 Number of system-generated and user-defined constraint names by constraint type (constraints that involve more than one column) Find the number of system-generated constraint names by constraint type. Names should follow the same style. If there is a mix of system-generated and user-defined names, then the style is most probably different. Sofware measure system catalog base tables only 2025-11-07 10:11 MIT License View
644 Number of system-generated and user-defined constraint names by constraint type (constraints that involve one column) Find the number of system-generated constraint names by constraint type. Names should follow the same style. If there is a mix of system-generated and user-defined names, then the style is most probably different. Sofware measure system catalog base tables only 2025-11-07 10:11 MIT License View
645 The number of table constraints with the cardinality bigger than one Find the number of table constraints with the cardinality bigger than one. Find the number, based on the cardinality, constraint type, and cardinality+constraint type. Sofware measure system catalog base tables only 2025-11-07 10:11 MIT License View
646 The number of tables by schema, by type, and in total Find the number of tables (base, foreign, and derived) in different schemas. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
647 The proportion of mandatory and optional textual base table columns Find the number of textual base table columns, the number of optional textual base table columns (permit NULLs), and the number of mandatory textual base table columns (do not permit NULLs). Sofware measure INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
648 The number of user-defined non-trigger routines by schema, by language, and by routine type Find the number of user-defined non-trigger routines based on their schema, language, and routine type. The routines can be used to implement virtual data layer. Thus the queriy gives some indications about the possible extent of the layer. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
649 The number of user-defined non-trigger routines by schema, by security type, and by being deterministic Find the number of user-defined non-trigger routines based on their schema, security type, and being deterministic. The routines can be used to implement virtual data layer. Thus the queriy gives some indications about the possible extent of the layer. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
650 The number of routines by the number of input parameters Find the number of user-defined non-trigger routines by the number of input parameters. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
651 The number of user defined triggers by different characteristics Find the number of user defined triggers by action orientation (ROW, STATEMENT), action timing (BEFORE, AFTER, INSTEAD OF), and event type (INSERT, UPDATE, DELETE) and their combinations. Sofware measure INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
652 The use of xmin hidden column in views and routines Find the number of views and materialized views that have a column with the xid type and the number of routines that contain a UPDATE or a DELETE statement that search condition refers to the xmin column. If one uses optimistic approach for dealing with the concurrent modifications of data, then xmin values should be presented by views and used in routines that modify or delete rows. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
653 Number of views with and without security barrier Find the number of views, the number of views with and without security barrier setting, and the names of views with and without the security barrier setting. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
654 Patterns of the names of columns of simple primary keys Find the patterns of the names of columns of simple primary keys. Make sure that the naming is consistent. Ideally, the names should indicate as to whether the column is a surrogate or a natural key column. PostgreSQL 14 added primary keys, unique constraints, and foreign keys to system catalogs. Thus the query was modified to exclude the results from the system catalog. General system catalog base tables only 2025-11-07 10:11 MIT License View
655 Patterns of the names of triggers and rules Find the patterns of the names of trigger and rule names. Make sure that the naming is consistent. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
656 All database privileges Find the privileges to use the database. c - CONNECT; C - CREATE; T - TEMPORARY. General system catalog base tables only 2025-11-07 10:11 MIT License View
657 The size of base tables and their indexes Find the size of base tables without indexes, size of the indexes of the table, total size of the table (including its indexes) and percentage of the index size from the total size. If the size of indexes of a table is relatively high, then check as to whether all the indexes are needed. General system catalog base tables only 2025-11-07 10:11 MIT License View
658 The longest names of database objects Find the TOP 3 longest (identifiers) names of user-defined objects. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
659 The number of columns based on table type Find the total number of columns in the different types of tables as well as average number of columns in the tables as well as minimal and maximal number of columns. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
660 The number of default values Find the total number of columns with a default value as well as the number of columns with different kinds of default values (the number of columns where the default is used to implement surrogate key, the number of columns where the default is not used to implement surrogate key, the number of columns with a static default value, the number of columns with a dynamic default value). Sofware measure INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View