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
841 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
842 The storage size of each index (the system catalog excluded) Get overview of disk usage. Sofware measure system catalog base tables only 2020-11-06 14:51 MIT License View
843 The storage size of each table (including indexes) (the system catalog excluded) Get overview of disk usage. Sofware measure system catalog base tables only 2020-11-06 14:51 MIT License View
844 The total storage size of all indexes (the system catalog excluded) Get overview of disk usage. Sofware measure system catalog base tables only 2020-11-06 14:51 MIT License View
845 The total storage size of the database (the system catalog included) Get overview of disk usage. Sofware measure system catalog base tables only 2020-11-06 14:51 MIT License View
846 The storage size of each schema data object (the system catalog excluded) Get overview of disk usage by different schema objects that contain user data. Sofware measure system catalog base tables only 2020-11-06 14:51 MIT License View
847 Data type usage in the base table columns Get overview of used data types in the columns of base tables. If the selection is very small then this is a warning sign that perhaps unsuitable types have been used. General INFORMATION_SCHEMA only 2023-12-22 12:39 MIT License View
848 Disabled rules Identify disabled rules. These should be enabled or dropped, otherwise these are dead code. Problem detection system catalog base tables only 2022-10-21 11:22 MIT License View
849 Disabled user triggers Identify disabled triggers. These should be enabled or dropped, otherwise these are dead code. Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View
850 Base tables and foreign tables that do not have any CHECK constraints on non-foreign key columns Identify possibly missing CHECK constraints. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-11-02 10:41 MIT License View
851 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
852 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
853 Triggers with SELECT (i.e., probably check data based on another table) If a trigger is used for enforcing a constraint, then it should take into account that due to the implementation of multiversion concurrency control (MVCC) in PostgreSQL, reading data does not block data modification and vice versa. Thus, there may be a need to lock the entire table or some row explicitly. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
854 Missing USAGE privileges on schema If a user has a privilege to use a schema object, then the user must also have the usage privilege on the schema that contains the object. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-21 12:06 MIT License View
855 Potentially missing PRIMARY KEY or UNIQUE constraints (based on UUIDs) If something has to be unique, then it must be said to the system so that it could use the information for internal optimizations and enforce the constraint. Find columns that contain Universally Unique Identifiers but are not a part of any simple PRIMARY KEY/UNIQUE constraint and are also not part of a foreign key. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-03 11:18 MIT License View
856 Incorrect prefix of a constraint name or an index name If the name of an object has the prefix that refers to the type of the object (for instance, primary key constraint or foreign key constraint), then you should use references to the correct object type. Find prefixes of constraint names and index names that incorrectly refer to the type of the object. For instance, incorrect would be to use chk_ as the prefix of an index name or pk_ as the prefix of a check constraint name. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-28 10:48 MIT License View
857 Incorrect suffix of a constraint name or an index name If the name of an object has the suffix that refers to the type of the object (for instance, primary key constraint or foreign key constraint), then you should use references to the correct object type. Find suffixes of constraint names and index names that incorrectly refer to the type of the object. For instance, incorrect would be to use _chk as the suffix of an index name or _pk as the suffix of a check constraint name. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-28 10:44 MIT License View
858 Parameter name is the same as the name of a used column If the name of a routine parameter and the name of a column of a table that is used in the routine are the same, then it makes it more difficult to understand the code. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-01-20 13:54 MIT License View
859 Parameter name is the same as the name of a used column (ver 2) If the name of a routine parameter and the name of a column of a table that is used in the routine are the same, then it makes it more difficult to understand the code. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-01-20 13:54 MIT License View
860 Privileges to use base table columns If you do give privileges to base tables, then these should follow the principle of least privilege - the smallest possible amount of privileges to the smallest possible set of columns General INFORMATION_SCHEMA+system catalog base tables 2020-12-29 10:38 MIT License View