PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1036 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 Rules with the same name in different schemas Find rule names that are used in a database in more than one schema. Different things should have different names. But here different rules have the same name. Also make sure that this is not a duplication. General system catalog base tables only 2025-11-07 10:11 MIT License View
182 Set operations that do not remove duplicate rows in derived tables Find derived tables (views and materialized views) that use a set theoretic operation (union, except or intersect) in a manner that does not remove duplicate rows and thus can produce a multiset not a set. Make sure that it is what is needed. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
183 Single-column natural primary keys This query identifies primary keys that consist of a single column and are not system-generated (i.e., they are not associated with a sequence or defined as IDENTITY columns). This pattern is characteristic of a natural primary key, where the key's value is derived from a real-world, user-defined attribute rather than an arbitrary surrogate value. Identifying these keys is crucial for auditing a data model's key strategy and understanding its reliance on meaningful, potentially mutable, business data for entity identification. General INFORMATION_SCHEMA+system catalog base tables 2025-11-15 12:50 MIT License View
184 Small tables Find tables that have one column or zero columns. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
185 Storing file content in the database Find columns that probably store content of files in the database. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
186 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 2025-11-07 10:11 MIT License View
187 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 2025-11-07 10:11 MIT License View
188 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 2025-11-07 10:11 MIT License View
189 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 2025-11-07 10:11 MIT License View
190 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 2025-11-07 10:11 MIT License View
191 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 2025-11-07 10:11 MIT License View
192 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 2025-11-07 10:11 MIT License View
193 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 2025-11-07 10:11 MIT License View
194 Table privileges Check as to whether there are no unnecessary privileges. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
195 Tables that have associated user triggers Find information about tables that are associated with triggers. General system catalog base tables only 2025-11-07 10:11 MIT License View
196 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 2025-11-07 10:11 MIT License View
197 The longest names of database objects Find the TOP 3 longest (identifiers) names of user-defined objects. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
198 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 2025-11-07 10:11 MIT License View
199 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 2025-11-07 10:11 MIT License View
200 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 2025-11-07 10:11 MIT License View