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
981 Using system-defined names of constraints (constraints that involve more than one column) Find the constraint types in case of which there exists system-defined names. Problem detection system catalog base tables only 2023-01-10 14:53 MIT License View
982 Using system-defined names of constraints (constraints that involve one column) Find the constraint types in case of which there exists system-defined names. Problem detection system catalog base tables only 2023-01-10 14:53 MIT License View
983 Using unreserved (in PostgreSQL) SQL keywords as the names of a database object (aggregate view) "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 not cause confusion. Find the distinct names (identifiers) of user-defined objects that are SQL keywords that are completely unreserved in PostgreSQL. In PostgreSQL "there are several different classes of tokens ranging from those that can never be used as an identifier to those that have absolutely no special status in the parser as compared to an ordinary identifier. " (PostgreSQL manual) Although the names are unreserved keywords in PostgreSQL these could be reserved keywords in other systems, which would complicate database migration. Moreover, such identifiers are often too general, i.e., do not provide enough information about the named object. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-21 17:17 MIT License View
984 Using unreserved (in PostgreSQL) SQL keywords as the names of a database object (detailed view) "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 not cause confusion. Find the names (identifiers) of user-defined objects that are SQL keywords that are completely unreserved in PostgreSQL. In PostgreSQL "there are several different classes of tokens ranging from those that can never be used as an identifier to those that have absolutely no special status in the parser as compared to an ordinary identifier. " (PostgreSQL manual) Although the names are unreserved keywords in PostgreSQL these could be reserved keywords in other systems, which would complicate database migration. Moreover, such identifiers are often too general, i.e., do not provide enough information about the named object. Think as to whether some better name would be possible. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-21 17:17 MIT License View
985 Vacuum and analyze status of base tables Find for each base table the last time when the table has been vacuumed or analyzed (either manually or automatically). General system catalog base tables only 2023-10-06 13:48 MIT License View
986 Validation method does not confirm Find user-defined SQL and PL/pgSQL routines that do not return a value although the name suggest that it should return a value (starts with "validate" or "check"). Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-01-06 14:25 MIT License View
987 Very similar (but not equal) routine names Find pairs of names of different types of routines that are very similar but not equal. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-11-30 12:09 MIT License View
988 Very similar column names Find the pairs of table columns that name is different only by one symbol and that have the same type and/or domain. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-03-18 20:25 MIT License View
989 Very similar domain names Find pairs of names of domains that are very similar or even equal. Problem detection INFORMATION_SCHEMA only 2024-11-30 12:07 MIT License View
990 Very similar table names Find pairs of names of different types of tables that are very similar or even equal. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-11-30 11:50 MIT License View
991 Views without security barrier Find views that do not have the security barrier option. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-13 19:30 MIT License View
992 Views with security invoker Find views that have the security invoker option. Such option is possible starting from PostgreSQL 15. In case of using such views one cannot create a system where data is accessed through views and the users (applications) do not have direct access to the base tables. Problem detection system catalog base tables only 2023-11-13 12:20 MIT License View
993 Views with the WITH LOCAL CHECK OPTION constraint Find updatable views that have WITH LOCAL CHECK OPTION constraint. The predicate of a view is the conjunction of the predicates of its (directly and indirectly) underlying tables (both base tables and derived tables) as well as the predicate of the view itself. In case of using WITH LOCAL CHECK OPTION constraint "New rows are only checked against the conditions defined directly in the view itself. Any conditions defined on underlying base views are not checked (unless they also specify the CHECK OPTION)." (PostgreSQL manual) Thus, use instead WITH CASCADED CHECK option to instruct the system to check new rows against the entire predicate of the view. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
994 Views with unnecessary security invoker Find views with security invoker option that do not have any underlying base table with a security policy. Security invoker option of views is possible starting from PostgreSQL 15. "The main use case (and the one that inspired the feature) is to be able to use views and still check row-level security policies on the underlying tables as the invoker." Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-17 18:39 MIT License View
995 Views with WHERE but without security barrier Find views that do not have the security barrier option but restrict rows in some way. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-13 19:29 MIT License View
996 Wrong comment - trigger function does not implement a database operation Trigger functions should not contain references to database operations. Perhaps the trigger implements ensuring some invariant of the operation but it does not implement the operation itself. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
997 YELLING IN COMMENTS! Find comments of derived tables and routines that consist of only uppercase letters. Do not use only uppercase (capital) letters in order to write comments. It means yelling and also makes text less readable. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-04-25 15:13 MIT License View