PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1018 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
861 The number of names that use different writing styles Find the number of names of user-defined database objects that use snake_case, ALL CAPS, Sentence case, camelCase or PascalCase style. Make sure that the naming style is consistent. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
862 The number of referencing base tables Find how many base tables reference to a base table by using foreign keys. The more references there are the more impact the change of the table could have to the overall system. Sofware measure system catalog base tables only 2025-11-07 10:11 MIT License View
863 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
864 The number of rules by different characteristics Find the number of rules by event (SELECT, INSERT, UPDATE, and DELETE) and in total. Sofware measure system catalog base tables only 2025-11-07 10:11 MIT License View
865 The number of schema objects by schema, by type, and in total Find the number of different types of schema objects in different schemas as well as the total number of schema objects. Show the number of objects that belong to an extension as well as the number of (user-defined) objects that do not belong to an extension. Sofware measure system catalog base tables only 2025-11-07 10:11 MIT License View
866 The number of sequence generators in different schemas Identify the number of sequence generators in different schemas. Surrogate key values must be generated by using the system (the sequence generator mechanism in case of PostgreSQL). If there are no sequence generators, then there is a question as to whether there are no surrogate keys in the database at all (could be possible and OK) or (more probable) developers have forgotten to implement the generation of surrogate keys at the database level. An alternative is to implement generation of unique values at the application level or at the database level by using triggers. However, such implementation would most probably lead to the performance penalty because adding new rows to the table must be serialized, i.e., it can be done in one session at a time. Sofware measure INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
867 The number of SQL-language routines with different types of bodies Find for each user schema that contain a SQL-language routine how many of these have the body that is string literal and how many have the body (possible starting from PostgreSQL 14) that is parsed at the routine definition time. Try to be consistent, i.e., use the same solution in case of all the routines. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
868 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
869 The number of tables based on the number of columns Find how many tables with a certain range of the number of columns there are in the database. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
870 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
871 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
872 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
873 The number of user-defined routines based on their database usage Find the number of non-extension routines in a database that modify data. The routines can be used to implement virtual data layer. Thus the queriy gives some indications about the possible extent of the layer. The query distinguishes trigger and non-trigger routines, thus it also gives information about triggers that modify data. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
874 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
875 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
876 The number of user-defined triggers Triggers can be used to maintain data integrity in a database by causing rejection of data that does not conform to certain rules. Therefore, the number of triggers in a database gives some indications about the state of enforcing constraints at the database level. The query does not count internal triggers. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
877 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
878 The number of user-defined triggers by schema, by type, and in total Triggers can be used to maintain data integrity in a database by causing rejection of data that does not conform to certain rules. Therefore, the number of triggers in a database gives an indication about the state of enforcing constraints at the database level. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
879 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
880 The proportion of using different integer types as types of base table columns Find the number of base table columns that use different integer types (SMALLINT, INTEGER, BIGINT) and their proportion from the overall set of columns that use an integer type. Sofware measure INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View