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
721 The number and percentage of different names of database objects Names should be expressive. Find the number of different names used in a database as well as the number of named database objects, and the percentage of different names from all the names. Names should be expressive. Different objects should have different names. The smaller the percentage the less descriptive are the names in the database. Find the number of different names (identifiers) of user-defined database objects and compare it with the total number of database objects. 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
722 The number and percentage of different names of database objects by 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. Different objects should have different names. The smaller the percentage the less descriptive are the names in the database. Find the number of different names (identifiers) of user-defined database objects by the object type and compare it with the total number of database objects with this 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
723 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
724 The number of commented routines Find the number of commented routines based on the way how the comments have been added (as a COMMENT object or as a comment in the routine body). Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
725 The number of constraints by schema, by type, and in total Find the number of constraints in different schemas. The number of constraints 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
726 The number of derived table columns with an array, a user-defined, XML, JSON, or JSONB type Find the number of derived table columns with json, xml, array, or user-defined types. Such columns may contain data that has been aggregated/composed based on values in base tables. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
727 The number of names of database objects by the number of subcomponents in the names "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 number of names (identifiers) of user-defined database objects by the number of subcomponents in the names. 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
728 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
729 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
730 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
731 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
732 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
733 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
734 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
735 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
736 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
737 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
738 The reference to a database operation is missing from a comment This query audits the metadata of user-defined routines to enforce traceability between the implementation and the design specifications. It identifies routines whose comments lack a standardized reference to the specific database operation contract they implement. The query checks for the absence of a required identifier pattern, typically formatted as OP followed by a number (e.g., OP1, OP12). Enforcing this standard ensures that every routine can be mapped back to its originating requirement or business rule. Problem detection INFORMATION_SCHEMA+system catalog base tables 2026-01-19 16:18 MIT License View
739 There is no reason to use PL/pgSQL if you do not use one or more features of a procedural language Using PL/pgSQL may cause context switching between declarative SQL and procedural PL/pgSQL. Thus use PL/pgSQL only if you truly need some of its constructs (variables, conditional statements, cycles, cursors, exception handling). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
740 There is no reason to use PL/pgSQL to write table functions Using PL/pgSQL may cause context switching between declarative SQL and procedural PL/pgSQL. Thus use PL/pgSQL only if you truly need some of its constructs. You can create table functions by using SQL. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View