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
821 Derived tables with ranking Find views and materialized views that use rank and dense_rank window functions. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
822 Derived tables with string_agg Find views and materialized views that use string_agg aggregate function. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
823 Too short or missing comments of derived tables and routines Find views, materialized views, and user-defined routines that do not have a comment at all or the comment is shorter than twice the length of the object name, or the comment states that it is missing (TODO). You should give information to future developers and maintainers of the system (including the future version of yourself). Do not just repeat the name in the comment (with perhaps some rewording). Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-01-19 12:56 MIT License View
824 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
825 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
826 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
827 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
828 Derived table uses a function to get data from another table Find views that use a function to get data from another table. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-24 14:36 MIT License View
829 Duplicate views Find views with exactly the same subquery. There should not be multiple views with exactly the same subquery. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
830 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
831 Non-foreign key columns that have no associated CHECK constraints Find what are the base table columns that are not foreign key columns and that have no associated CHECK constraints? Perhaps some CHECK constraints are missing. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View
832 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 2024-12-21 16:42 MIT License View
833 Do not always depend on one's parent Find where a hierarchical structure is implemented in a base table by adding a foreign key that refers to a candidate key of the same table. General system catalog base tables only 2021-03-12 15:36 MIT License View
834 Do not always depend on one's parent - column names are ot sufficiently different Find where a hierarchical structure is implemented in a base table by adding a foreign key that refers to a candidate key of the same table. Find only cases where the candidate key and foreign key column names are very similar (Levenshtein distance shorter than four). Problem detection system catalog base tables only 2022-11-26 17:19 MIT License View
835 Do not always depend on one's parent (INFORMATION_SCHEMA) Find where a hierarchical structure is implemented in a base table by having a foreign key that refers to a candidate key of the same table. This design is called adjacency list. General INFORMATION_SCHEMA only 2021-03-07 10:57 MIT License View
836 Inconsistent data type usage in case of registering a symbol Find whether the database uses both CHAR(1) and VARCHAR(1) columns to register a single symbol. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
837 Double negatives in regular expressions Fing regular expression patterns that use [^\S] instead of \s or [^\D] instead of \d or [^\W] instead of \w. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-09 12:01 MIT License View
838 Number of columns covered with constraints For different types of constraints find the number of columns covered with constraints of such type. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2021-10-16 11:01 MIT License View
839 Input parameters that names do not follow the convention to start with _ or p_ For the sake of making code better understandable follow naming conventions. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
840 Names of database objects that are fully uppercase Full uppercase means screaming and it makes comprehending the names more difficult. Find the names (identifiers) of user-defined database objects that are fully uppercase. Because PostgreSQL stores regular identifiers lowercase in the system catalog it also means that these are delimited identifiers, i.e., these are case sensitive. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-21 17:27 MIT License View