PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 997 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
961 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 2020-11-06 14:51 MIT License View
962 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 2021-11-22 13:26 MIT License View
963 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 2023-01-14 20:22 MIT License View
964 The number of direct and indirect child tables (foreign keys) Find the number of direct and all (direct+indirect) child tables of a base table based on the foreign keys. The result may help to classify the data. If the number of direct children is small but the number of all children is large, then the table probably contains classifiers. If both the number of direct and all children is large, then the table probably contains master data. If the number of all children is small, then the table probably contains transactional data, logs, or descriptive information of main entities. Sofware measure system catalog base tables only 2020-11-14 16:10 MIT License View
965 The number of direct and indirect child tables (table inheritance) Find the number of direct and all (direct+indirect) child tables of a base table based on the table inheritance. Sofware measure system catalog base tables only 2020-11-14 19:59 MIT License View
966 The number of domains by schema and in total Identify the number of domains in different schemas. Sofware measure INFORMATION_SCHEMA only 2020-11-06 14:51 MIT License View
967 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 2024-12-21 17:25 MIT License View
968 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 2024-12-21 17:22 MIT License View
969 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 2021-03-12 11:02 MIT License View
970 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 2024-01-16 09:43 MIT License View
971 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 2024-01-14 16:10 MIT License View
972 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 2020-11-06 14:51 MIT License View
973 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 2021-03-07 20:55 MIT License View
974 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 2022-12-02 11:33 MIT License View
975 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 2020-11-19 13:29 MIT License View
976 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 2021-03-25 11:17 MIT License View
977 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 2021-03-07 10:57 MIT License View
978 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 2024-01-14 20:53 MIT License View
979 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 2024-01-14 20:52 MIT License View
980 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 2024-01-15 00:38 MIT License View