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
761 Unused indexes Find indexes that are not used by the DBMS. Remember that indexes are not a "free lunch" and they slow down the processes of updating data. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-01-19 00:23 MIT License View
762 Unused indexes (2) Find indexes that are not used by the DBMS. Remember that indexes are not a "free lunch" and they slow down the processes of updating data. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-08-07 16:23 MIT License View
763 Unused named input parameters Find named input parameters that are not referenced in the routine body. All the parameters that are presented in the routine signature declaration must be used in its body. Otherwise these are dead code elements. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-05 13:03 MIT License View
764 Unused trigger functions Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
765 Updatable views that have not been turned to read only Find views that are theoretically updatable but do not have INSTEAD OF trigger or DO INSTEAD NOTHING rule to prevent data modifications through the view. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-10-29 10:20 MIT License View
766 Update prevention may prevent legal updates Find triggers that try prevent updating data in a certain column but prevent also certain legal updates - updates that write to a field a value that was in the field before the update. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-07-07 13:12 MIT License View
767 Updating or deleting data in a routine without restricting rows Find user-defined routines that contain UPDATE or DELETE statement but do not contain any WHERE clause, meaning that at least one UPDATE or DELETE operation influences all the rows of a table. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-29 13:21 MIT License View
768 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 2024-01-14 00:50 MIT License View
769 Used indexes Find indexes that are used by the DBMS. General INFORMATION_SCHEMA+system catalog base tables 2024-08-07 16:23 MIT License View
770 Useless type indication Find columns and parameters where the type of the identifier is perhaps explicitly indicated in the name. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-10 14:47 MIT License View
771 Useless type indication (2) Find columns and parameters where the type of the identifier is perhaps explicitly indicated in the name. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-10 14:55 MIT License View
772 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 2023-12-11 15:23 MIT License View
773 User-defined non-trigger routines without parameters Find user-defined non-trigger routines with no parameters. General INFORMATION_SCHEMA+system catalog base tables 2021-11-03 20:05 MIT License View
774 User-defined non-trigger SQL and PL/pgSQL routines Find the non-trigger SQL and PL/pgSQL routines in non-systemic schemas. General INFORMATION_SCHEMA+system catalog base tables 2024-12-19 10:54 MIT License View
775 User-defined routine execution privilege has been granted to PUBLIC You should follow the principle of least privilege and thus not have in your database user-defined routines that execution privilege is granted to PUBLIC, i.e., to all the database users now and in the future. By default, PostgreSQL gives routine execution privileges to PUBLIC. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
776 User-defined routines that have the same name as some system-defined routine. Avoid creating user-defined routines that have the same name as some system-defined routine because it may cause confusion. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
777 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 2021-10-25 17:02 MIT License View
778 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 2021-10-25 16:25 MIT License View
779 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 2023-12-21 15:00 MIT License View
780 User-defined routines that use dynamic SQL to execute data manipulation statements Find user-defined routines that use dynamic SQL to execute data manipulation statements (SELECT, INSERT, UPDATE, DELETE). Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-04 12:39 MIT License View