PostgreSQL Query Catalog

Find the number of rules by event (SELECT, INSERT, UPDATE, and DELETE) 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.

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.

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.

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.

Find how many tables with a certain range of the number of columns there are in the database.

Find the number of tables (base, foreign, and derived) in different schemas.

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.

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.

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.

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.

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.

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.

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.

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.

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).

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.

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).

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.

Find the names of table CHECK constraints that have the same Boolean expression but a different naming style in different places (tables). The naming of constraints should be consistent. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code)x