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 Base tables that do not have a TOAST table Find base tables that (due to the types of their columns) do not have an associated TOAST table for storing out-of-line data. General system catalog base tables only 2020-11-06 14:51 MIT License View
962 Candidate keys and foreign keys of tables that participate in an inheritance hierarchies Find primary key, unique, foreign key, and exclude constraints that have been defined in tables that participate in an inheritance hierarchy. Do not forget to redefine the constraints that are defined on supertables also on their subtables. General system catalog base tables only 2020-11-06 14:51 MIT License View
963 CHECK constraints that use non-deterministic functions Discover incorrect usage of non-deterministic functions in CHECK constraints. Find base table columns and foreign table columns that have a CHECK constraint that refers to a non-deterministic function that returns current date/time/timestamp. General INFORMATION_SCHEMA only 2020-11-06 14:51 MIT License View
964 Comments of routines Find comments of user-defined routines (functions or procedures) that are registered in the system catalog witht a COMMENT statement. Make sure that the comments give relevant, useful, and correct information. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
965 Composite foreign keys Find foreign keys that consist of more than one column. Make sure that the order of columns in the composite foreign key corresponds to the order of columns in the composite candidate key in the referenced table. General system catalog base tables only 2020-11-06 14:51 MIT License View
966 Declaratively partitioned tables with one partition Find declaratively partitioned tables that have exactly one partition. Declarative partitioning is implemented in PostgreSQL starting from PostgreSQL 10. If there is only one partition, then it raises question as to why the additional complexity associated with partitioning is needed. Problem detection INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
967 Derived tables that have a column with the xid type Find the derived tables (views and materialized views) that have a column with the xid type, i.e., these use the data from the hidden xmin column of a base table. If one uses optimistic approach for dealing with the concurrent data modifications, then xmin values should be presented by views and used in routines that modify or delete rows. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
968 Derived tables that present data in json or xml format Find views and materialized views that present data in json or xml format. Instead of recording data in this format in base tables one could generate the data value on the fly based on data that has been recorded in base tables. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
969 Derived tables with ranking Find views and materialized views that use rank and dense_rank window functions. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
970 Derived tables with string_agg Find views and materialized views that use string_agg aggregate function. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
971 Domain candidates Find column descriptions that are candidates for describing a domain. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
972 Domain usage in base tables Find for each domain the number of usages in base tables. The less you have managed to reuse domains in case of different columns, the more you have wasted your time by creating the domains. General INFORMATION_SCHEMA only 2020-11-06 14:51 MIT License View
973 Extension routines Find all routines that belong to an extension. General system catalog base tables only 2020-11-06 14:51 MIT License View
974 Extensions that are available but are not installed Try to use as much the possibilities of the DBMS as possible. On the other hand, do not install extensions that are not needed in order not to overcomplicate the database. General system catalog base tables only 2020-11-06 14:51 MIT License View
975 Generated stored base table columns Find generated stored columns in PostgreSQL base tables. The support of generated columns was added to PostgreSQL 12. These could be used to improve the performance of queries. General INFORMATION_SCHEMA only 2020-11-06 14:51 MIT License View
976 Grantees Database must be used by users who have minimal set of privileges for performing tasks. The query helps to find out as to whether some user/role other than PUBLIC and a superuser have rights to use tables and routines of the database. General INFORMATION_SCHEMA only 2020-11-06 14:51 MIT License View
977 Installed extensions Try to use as much the possibilities of the DBMS as possible. On the other hand, do not install extensions that are not needed in order not to overcomplicate the database. General system catalog base tables only 2020-11-06 14:51 MIT License View
978 Minimum tuple length required before trying to move long column values into TOAST tables has been changed Find base tables in case of which toast_tuple_target storage parameter value is not the default value (2040). "Changing this value may not be useful for very short or very long rows. Note that the default setting is often close to optimal, and it is possible that setting this parameter could have negative effects in some cases. " Make sure that the parameter has an optimal value. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
979 Partial or case insensitive unique indexes Find partial or case insensitive unique indexes. These implement uniqueness constraints that are impossible to enforce with the help of SQL's regular UNIQUE constraint. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
980 Percentage of the total index storage size from the total database storage size (system catalog included) Get overview of disk usage. Sofware measure system catalog base tables only 2020-11-06 14:51 MIT License View