PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1041 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
201 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 2025-11-07 10:11 MIT License View
202 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 2025-11-07 10:11 MIT License View
203 Triggers with arguments from the CREATE TRIGGER statement Find triggers that get an argument from the CREATE TRIGGER statement. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
204 Triggers with SELECT (i.e., probably check data based on another table) This query identifies trigger functions intended for constraint enforcement that are susceptible to concurrency anomalies due to PostgreSQL's Multi-Version Concurrency Control (MVCC) model. Since read operations (SELECT) do not block write operations, a trigger that validates cross-row constraints without acquiring explicit locks (e.g., LOCK TABLE or SELECT ... FOR UPDATE) involves a race condition. The query detects triggers that query auxiliary data. General INFORMATION_SCHEMA+system catalog base tables 2026-01-19 15:19 MIT License View
205 Triggers with the same name in different schemas Find trigger names that are used in a database in more than one schema. Different things should have different names. But here different triggers have the same name. Also make sure that this is not a duplication. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
206 Unlogged tables Find unlogged tables. These may improve the performance of INSERT operations, but with the price of possibly loosing data - an unlogged table is automatically truncated after a crash or unclean shutdown. General system catalog base tables only 2025-11-07 10:11 MIT License View
207 Updatable foreign tables that refer to another PostgreSQL table This query identifies foreign tables established via the postgres_fdw (PostgreSQL Foreign Data Wrapper) that are configured to permit data modification (updatability). While postgres_fdw supports INSERT, UPDATE, and DELETE operations on remote tables, enabling this capability introduces complexity regarding distributed transactions, performance, and security. The query serves as an audit tool to verify that the updatability of these foreign tables is a deliberate architectural requirement and not an unintended default configuration. General INFORMATION_SCHEMA only 2025-11-20 11:45 MIT License View
208 UPDATE triggers Find all UPDATE triggers. Make sure that they specify a correct set of columns in which data modification will fire the trigger. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
209 Usage of base tables Find for each derived table the list of base tables that are used by the derived table. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
210 Used indexes Find indexes that are used by the DBMS. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
211 User-defined composite types Find composite types that are created by a user, i.e., the type is not created automatically by the database management system based on a relation. General system catalog base tables only 2025-11-07 10:11 MIT License View
212 User-defined derived tables Find user-defined views and materialized views. Pay attention to the outer join operations. One should use these if and only if there is a real need for them. Otherwise they just reduce performance. On the other hand, pay attention that outer join is used where it is logically needed. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
213 User-defined non-trigger routines without parameters Find user-defined non-trigger routines with no parameters. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
214 User-defined non-trigger SQL and PL/pgSQL routines This query retrieves a comprehensive list of user-defined routines (functions and procedures) written in SQL or PL/pgSQL. It explicitly filters the result set to exclude:

  • Trigger functions: Routines intended solely for use in triggers are omitted to focus on callable business logic.
  • System schemas: Routines located in system-managed namespaces (e.g., pg_catalog, information_schema) are excluded to isolate user-created code.
  • Extension routines.

The result provides an inventory of the application's explicit, callable database logic.		 General INFORMATION_SCHEMA+system catalog base tables 2025-11-30 08:41 MIT License View
215 User-defined routines that implement UPSERT operation Find user-defioned routines that implement UPSERT operation. Make sure that it is consistent with the contracts of database operations. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
216 User-defined routines that produce a temporary table Find user user-defined routines that produce a temporary table General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
217 User-defined routines that read data Find user-defined routines that contain SELECT … FROM or PERFORM … FROM operations. PostgreSQL uses multiversion concurrency control (MVCC). Therefore, SELECTs do not block modifications and vice versa. One has to take steps to achieve correct behaviour of data access code. In addition, one should not ask data with multiple queries if it is possible to achieve the result with only one query. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
218 User-defined routines that use xmin hidden column Find routines that contain a UPDATE or a DELETE statement that search condition refers to the xmin column. If one uses optimistic approach for dealing with the concurrent modifications of data, then xmin values should be presented by views and used in routines that modify or delete rows. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
219 User-defined routines with dynamic SQL Find routines that use dynamic SQL. Make sure that dynamic SQL is indeed needed, i.e., the task cannot be solved with static SQL. Make sure that the routine is protected against attacks that use SQL injection method. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
220 User-defined routines with the same parameters (same name and type) regardless of the order of parameters Find routines with the same parameters (same name and type) regardless of the order of parameters. Make sure that there is no accidental duplication. The query helps users to group together routines that probably have related tasks. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View