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
181 Simple natural primary keys Find primary keys that consist of one column and that values are not generated by the system. General INFORMATION_SCHEMA+system catalog base tables 2021-03-08 00:47 MIT License View
182 Small tables Find tables that have one column or zero columns. General INFORMATION_SCHEMA+system catalog base tables 2021-03-07 10:52 MIT License View
183 Storing file content in the database Find columns that probably store content of files in the database. General INFORMATION_SCHEMA only 2021-03-27 17:02 MIT License View
184 Surrogate key columns Find surrogate keys. Surrogate key is a key that consist of one column, which has an integer type. The key has been declared by using PRIMARY KEY or UNIQUE constraint. The column is associated with a sequence generator (either external or internal, i.e., created by the system automatically because the column has been declared as an identity column). The column does not participate in any foreign key. General INFORMATION_SCHEMA+system catalog base tables 2021-03-07 20:59 MIT License View
185 Table check constraints with regular expressions Find all CHECK constraints (except NOT NULL) that are associated with a base table or a foreign table column and use a regular expression. It is useful to enforce as many constraints at database level as possible. In this way one improves data quality as well as gives extra information to the database users (including the DBMS engines, development environments, and applications). General INFORMATION_SCHEMA only 2022-12-13 12:47 MIT License View
186 Table columns that are associated with a sequence generator Surrogate key values must be generated by using the system (the sequence generator mechanism in case of PostgreSQL). If there is no usage of 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. General INFORMATION_SCHEMA only 2021-03-07 21:06 MIT License View
187 Table constraints with the cardinality bigger than one Find constraints that involve more than one columns. Check as to whether the names follow a common style or not. General system catalog base tables only 2020-11-19 13:34 MIT License View
188 Table has both state and status columns Find tables that contain both a state and a status column. General INFORMATION_SCHEMA+system catalog base tables 2023-01-14 15:26 MIT License View
189 Table has multiple columns for free-form descriptions Find tables that contain multiple columns for free-form textual descriptions. Make sure that the names of columns are understandable and sufficiently different. Make sure that there are no duplicate columns. General INFORMATION_SCHEMA+system catalog base tables 2023-01-14 15:36 MIT License View
190 Table inheritance Find inheritance between base tables. Use table inheritance carefully because, for instance, certain constraints are not inherited and must be redefined on child tables. General system catalog base tables only 2020-11-15 13:02 MIT License View
191 Table inheritance (path view) Find in case of each base table that participates in a table inheritance hierarchy the path to the table from the top-level table. Use table inheritance carefully because, for instance, certain constraints are not inherited and must be redefined on child tables. Also make sure that the identifier of each child table in an inheritance hierarchy is a hyponym of the identifier of its parent table. General system catalog base tables only 2022-11-13 16:13 MIT License View
192 Table privileges Check as to whether there are no unnecessary privileges. General INFORMATION_SCHEMA only 2020-12-29 10:38 MIT License View
193 Tables that have associated user triggers Find information about tables that are associated with triggers. General system catalog base tables only 2020-11-06 14:51 MIT License View
194 Tables with the same name in different schemas Find tables with the same name in different schemas. Make sure that this is not a duplication. General INFORMATION_SCHEMA+system catalog base tables 2021-11-10 15:40 MIT License View
195 The longest names of database objects Find the TOP 3 longest (identifiers) names of user-defined objects. General INFORMATION_SCHEMA+system catalog base tables 2024-12-21 17:26 MIT License View
196 The same database object name is used repeatedly in case the same database object type Find what database object names are used more than once in case the objects of the same type. If the names differ from each other only by digits or underscores, then consider these the same name. For instance, if there are base tables Person and Person2 (in the same schema or different schemas), then the query returns the name Person. Make sure that there is no duplication of implementation elements in the database. General INFORMATION_SCHEMA+system catalog base tables 2024-12-21 16:42 MIT License View
197 The same trigger function is used in case of multiple tables Find trigger functions that are used in case of more than one table. Although it is legal, one must be careful when changing the functions in order to avoid unwanted consequences. General INFORMATION_SCHEMA only 2020-12-25 14:50 MIT License View
198 The shortest names of database objects by object type "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 shortest (identifiers) names of user-defined objects by their type. These could be the first candidates of renaming in order to give to database objects better names. General INFORMATION_SCHEMA+system catalog base tables 2024-12-21 17:24 MIT License View
199 The size of base tables and their indexes Find the size of base tables without indexes, size of the indexes of the table, total size of the table (including its indexes) and percentage of the index size from the total size. If the size of indexes of a table is relatively high, then check as to whether all the indexes are needed. General system catalog base tables only 2023-10-27 20:38 MIT License View
200 The usage of data type formatting functions Find expressions that use a data type formatting function - to_char, to_number, to_date, to_timestamp. General INFORMATION_SCHEMA+system catalog base tables 2024-12-25 19:57 MIT License View