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
121 Do not always depend on one's parent (INFORMATION_SCHEMA) Find where a hierarchical structure is implemented in a base table by having a foreign key that refers to a candidate key of the same table. This design is called adjacency list. General INFORMATION_SCHEMA only 2021-03-07 10:57 MIT License View
122 Exclude constraint to prevent overlapping time periods Find exclude constraints on base tables with multiple date/timestamp columns that prevent overlapping time periods. General INFORMATION_SCHEMA+system catalog base tables 2023-11-07 00:44 MIT License View
123 Explicit locking PostgreSQL uses Multi-version Concurrency Control (MVCC) and thus, sometimes, one has to explicitly lock certain rows or entire table. One has to use LOCK TABLE or SELECT … FOR UPDATE statements for that. General INFORMATION_SCHEMA+system catalog base tables 2023-12-11 15:02 MIT License View
124 Extension routines Find all routines that belong to an extension. General system catalog base tables only 2020-11-06 14:51 MIT License View
125 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
126 Find all non-foreign key columns of base tables Find all non-foreign key columns of base tables. Make sure that no foreign key constraint is missing. General INFORMATION_SCHEMA+system catalog base tables 2020-11-21 02:59 MIT License View
127 Find all publications Find publications of tables that have been created in order to enable logical replication. General INFORMATION_SCHEMA+system catalog base tables 2020-11-18 15:09 MIT License View
128 Foreign key columns that do not have an integer or varchar type Find foreign key columns that do not have smallint, integer, bigint, or varchar(n) type. These are the most commonly used types in case of key/foreign key columns. Although the use of other types would be perfectly legal as well, make sure that you have selected the best possible data type for each and every column. General INFORMATION_SCHEMA+system catalog base tables 2020-11-16 10:14 MIT License View
129 Foreign keys with ON DELETE CASCADE Find referential constraints (foreign key constraints) that employ ON DELETE CASCADE compensatory action. ON DELETE CASCADE should only be used if it has been created based on a generalization or a composition in the conceptual data model or if the foreign key connects a table that corresponds to the main entity type with a table that corresponds to a non-main entity type. General system catalog base tables only 2020-11-16 10:15 MIT License View
130 Foreign keys with ON UPDATE CASCADE Referential constraints (foreign key constraints) that employ ON UPDATE CASCADE compensatory action. ON UPDATE CASCADE should only be used if the referenced key is a natural key (its values can be changed). General system catalog base tables only 2020-11-15 15:39 MIT License View
131 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
132 Granted roles Find membership relations between roles. General system catalog base tables only 2024-01-07 13:30 MIT License View
133 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
134 Index FILLFACTOR is not default Find all indexes where FILLFACTOR is not default, i.e., it has been changed. The default is different in case of different index types is different. In case of B-tree indexes the default is 90. General system catalog base tables only 2024-11-21 09:28 MIT License View
135 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
136 Mandatory non-primary key columns Find mandatory non-primary key columns, i.e., the columns that have NOT NULL constraint. General INFORMATION_SCHEMA+system catalog base tables 2020-12-05 19:35 MIT License View
137 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
138 More than one index on a column Find base table columns that belong to more than one index (including automatically created indexes that support constraints). General system catalog base tables only 2021-11-10 14:44 MIT License View
139 Multiple inheritance Find instances of multiple inheriance of base tables. Make sure that multiple inheritance is indeed needed. General system catalog base tables only 2020-11-15 12:20 MIT License View
140 Name and description maximum length Find tables where is both a column for registering name and description. Find the permitted maximum field size in these columns. Take into account that the maximum length may be controlled by using a CHECK constraint. Make sure that the permitted maximum field sizes are sufficiently different. General INFORMATION_SCHEMA+system catalog base tables 2021-02-24 20:36 MIT License View