PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1050 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
321 Empty columns Find columns in non-empty tables that do not contain any values. If there are no values in a columns, then it may mean that one hasn't tested constraints that have been declared to the column or implemented by using triggers. It could also mean that such columns are not needed at all. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
322 Empty schemas This query identifies empty schemas within the database. A schema is considered empty if it exists as a namespace but contains no database objects, such as tables, views, functions, or types. The presence of such schemas often indicates artifacts from failed or incomplete migrations, obsolete application components, or setup errors, and they can be safely removed to reduce schema clutter. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-13 14:15 MIT License View
323 Empty tables Find base tables where the number of rows is zero. If there are no rows in a table, then it may mean that one hasn't tested constraints that have been declared to the table or implemented by using triggers. It could also mean that the table is not needed because there is no data that should be registered in the table. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
324 Enumerated or range types with the same name in different schemas This query enforces the Don't Repeat Yorself principle across the database's type system. It identifies ENUM and RANGE types that share the same name but exist in different schemas. This indicates that a conceptual data type has been defined multiple times instead of having a single, canonical definition in a shared schema. Such duplication leads to maintenance overhead and the risk of semantic divergence over time. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 12:38 MIT License View
325 Enumerated types with zero or one value Fidn enumerated types with zero or one value. Type is a named finite set of values. The empty set is a set. A set with one value is a set. Thus, types with zero or one value are legal. In practical terms each type, usually, should contain at least two values. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
326 Excessive classifier name length This query identifies classifier tables where the name column allows strings longer than 100 symbols. Such long names are difficult to read and can cause layout issues in the user interface. Problem detection INFORMATION_SCHEMA+system catalog base tables 2026-05-05 17:31 MIT License View
327 Excessive data types for classifier codes This query identifies state, type or category code columns that use unnecessarily large numeric data types, specifically integer or bigint. It locates these columns by matching specific naming patterns in English and Estonian. Since reference tables typically contain a limited number of rows, these code columns should ideally be defined as smallint to optimize storage space and improve performance. Problem detection INFORMATION_SCHEMA only 2026-05-09 12:58 MIT License View
328 Excessive locking with FOR UPDATE in subqueries This query identifies performance and concurrency bottlenecks caused by excessive locking. It flags INSERT, UPDATE, or DELETE statements that utilize subqueries containing the FOR UPDATE clause. Using FOR UPDATE acquires an exclusive lock, which is semantically inappropriate if the rows in the subquery are merely being read for reference or validation rather than being modified. This practice degrades system concurrency by unnecessarily blocking other transactions. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-15 11:26 MIT License View
329 Excessive privileges on databases, schemas, domains, types, languages, foreign data wrappers, and foreign servers Find excessive privileges on databases, schemas, domains, collations, sequences, foreign data wrappers, and foreign servers that are probably not needed by a typical application. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
330 Exclude constraint instead of simple UNIQUE Find exclude constraints that implement a simple UNIQUE constraint. The checking might be slower compared to UNIQUE constraint. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
331 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 2025-11-07 10:11 MIT License View
332 Explicit locking This query identifies user-defined routines that employ explicit locking mechanisms to supplement PostgreSQL's default Multi-Version Concurrency Control (MVCC). It detects the presence of table-level locking (LOCK TABLE) or explicit row-level locking clauses (e.g., SELECT ... FOR UPDATE, FOR SHARE). While MVCC generally provides sufficient isolation for concurrent transactions, explicit locking is necessary in specific race-condition scenarios. This inventory assists in auditing concurrency control strategies and detecting potential sources of deadlocks or serialization bottlenecks. General INFORMATION_SCHEMA+system catalog base tables 2025-12-10 13:13 MIT License View
333 Explicit locking is missing This query identifies concurrency risks in user-defined routines by flagging INSERT, UPDATE, or DELETE statements that utilize subqueries without a FOR SHARE locking clause. Failure to acquire a shared lock on source rows allows concurrent transactions to modify or delete them between the subquery's execution and the outer operation, potentially leading to data inconsistencies. Routines utilizing the xmin system column are excluded, operating on the assumption that they implement Optimistic Concurrency Control (version checking) and therefore do not require pessimistic locking. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-15 12:45 MIT License View
334 Explicit locking is missing (2) (ChatGPT version) This query identifies concurrency risks in user-defined routines by flagging INSERT, UPDATE, or DELETE statements that utilize subqueries without a FOR SHARE locking clause. Failure to acquire a shared lock on source rows allows concurrent transactions to modify or delete them between the subquery's execution and the outer operation, potentially leading to data inconsistencies. Routines utilizing the xmin system column are excluded, operating on the assumption that they implement Optimistic Concurrency Control (version checking) and therefore do not require pessimistic locking. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-15 12:41 MIT License View
335 Extension routines Find all routines that belong to an extension. General system catalog base tables only 2025-11-07 10:11 MIT License View
336 Extension routines in the schema "public" Find extensions that routines are in the schema public. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
337 Extension routines that execution privilege has been granted to PUBLIC Know the privileges that users have in your system. Probably all the database users do not need these privileges. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
338 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 2025-11-07 10:11 MIT License View
339 Extreme contraction Find names that contain extremely short terms, due to an excessive word contraction, abbreviation, or acronym usage. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
340 Field size is not sufficiently different in case of base table columns Find base table columns that name refers to the possibility that these are used to register names. Find base table columns that name refers to the possibility that these are used to register comments/descriptions/etc. Find the cases where a base table contains columns from the both sets and the field size in case of the latter is not at least twice as big as in case of the former. For example, if the name of a service can be 100 character long, then it is quite illogical that the description of the service is only at most 200 characters long. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View