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
881 Name and description maximum length Find tables where is both a column for registering name and description. Find the permitted maximum field size in these columns. Take into account that the maximum length may be controlled by using a CHECK constraint. Make sure that the permitted maximum field sizes are sufficiently different. General INFORMATION_SCHEMA+system catalog base tables 2021-02-24 20:36 MIT License View
882 Base tables with multiple Boolean columns Find base tables that have more than one column with Boolean type. General INFORMATION_SCHEMA only 2021-02-19 17:41 MIT License View
883 Base tables with multiple temporal columns Find base tables that have more than one column with a temporal type (date or timestamp). General INFORMATION_SCHEMA only 2021-02-19 17:37 MIT License View
884 All domain default values Find domains that specify a default values and columns that are defined based on the domain. Make sure that there are no unsuitable default values. General INFORMATION_SCHEMA only 2021-01-19 13:04 MIT License View
885 All user triggers that are associated with tables Find user-defined triggers that react to data modifications in tables. Triggers should be used only for the tasks that cannot be achieved in a declarative manner, i.e., by declaring a constraint. Triggers of the same table with the same event_manipulation, action_timing, and action_orientation are sorted based on the trigger name. This is the order of execution of triggers. General INFORMATION_SCHEMA+system catalog base tables 2021-01-19 11:27 MIT License View
886 The proportion of mandatory and optional textual base table columns Find the number of textual base table columns, the number of optional textual base table columns (permit NULLs), and the number of mandatory textual base table columns (do not permit NULLs). Sofware measure INFORMATION_SCHEMA only 2021-01-15 17:39 MIT License View
887 Columns defined in a subtable Find columns that have been added to a subtable, i.e., these were not defined in its immediate supertable. General INFORMATION_SCHEMA+system catalog base tables 2021-01-02 03:22 MIT License View
888 Privileges to execute routines Find privileges to execute routines that have been given to non-superusers. Check as to whether it conforms to the principle of least privilege. Check that users that correspond to applications have all the necessary privileges. Users (applications) should use a database through virtual data layer. Thus, if they need to modify data in the database (in case of table functions read data), then they must execute a routine. General INFORMATION_SCHEMA+system catalog base tables 2020-12-29 10:38 MIT License View
889 Privileges to use base table columns If you do give privileges to base tables, then these should follow the principle of least privilege - the smallest possible amount of privileges to the smallest possible set of columns General INFORMATION_SCHEMA+system catalog base tables 2020-12-29 10:38 MIT License View
890 Privileges to use base tables Users (applications) should ideally use a database through virtual data layer and thus not directly use base tables. If there is a need to provide direct access to the base tables, then one should grant access based on the principle of least privilege, i.e., to the minimum possible number of base tables. General INFORMATION_SCHEMA+system catalog base tables 2020-12-29 10:38 MIT License View
891 Privileges to use views Find privileges to use views. Check as to whether it conforms to the principle of least privilege. Check that users that correspond to applications have all the necessary privileges. Users (applications) should use a database through virtual data layer. Thus, if they need to read data from a database, then they should use views. General INFORMATION_SCHEMA+system catalog base tables 2020-12-29 10:38 MIT License View
892 Table privileges Check as to whether there are no unnecessary privileges. General INFORMATION_SCHEMA only 2020-12-29 10:38 MIT License View
893 Consistency of using NOT NULL constraints on Boolean base table columns Find the number of mandatory and optional Boolean base table columns and the proportion of optional columns from all the Boolean columns. Use two-valued logic (TRUE, FALSE) instead of three-valued logic (TRUE, FALSE, UNKNOWN). Because NULL in a Boolean column means unknown make all the Boolean columns mandatory. Sofware measure INFORMATION_SCHEMA only 2020-12-28 01:43 MIT License View
894 Not inherited CHECK constraints Find CHECK constraints that have been defined in a supertable (parent table) but not in its subtables. An entity that belongs to a subtype should also belong to its supertype. If a subtype entity satisfies some constraint, then logically it must also satisfy the constraints of the supertype as well. If CHECK constraints are not inherited, then this is not guaranteed. If you implement subtyping not merely reuse implementation in the subtables, then the subtables must have at least the same CHECK constraints as the supertable. CHECK(false) on a supertable is an appropriate CHECK constraint if one wants to prevent registering data directly to the supertable, i.e., data can only be added to the subtables. General system catalog base tables only 2020-12-27 17:42 MIT License View
895 CHECK constraints on columns with Boolean data Find check constraints that involve columns with the type Boolean. General INFORMATION_SCHEMA only 2020-12-27 15:09 MIT License View
896 Constraints that are not redefined in a subtable but there is a CHECK constraint that compensates this Find primary key, unique, foreign key, and exclude constraints that have been defined in a supertable (parent table) but not in its subtable. Exclude constraints where in case of the subtable there is a CHECK that permits only one specific value in the constraint column. The presence of such check would make the design acceptable. Unfortunately, PostgreSQL table inheritance is implemented in a manner that some constraints (CHECK, NOT NULL) are inherited from the supertable but others are not. "All check constraints and not-null constraints on a parent table are automatically inherited by its children, unless explicitly specified otherwise with NO INHERIT clauses. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited." (PostgreSQL documentation) General INFORMATION_SCHEMA+system catalog base tables 2020-12-25 16:20 MIT License View
897 The same trigger function is used in case of multiple tables Find trigger functions that are used in case of more than one table. Although it is legal, one must be careful when changing the functions in order to avoid unwanted consequences. General INFORMATION_SCHEMA only 2020-12-25 14:50 MIT License View
898 All event triggers Find event triggers, which are not associated to a specific schema object. General system catalog base tables only 2020-12-24 14:54 MIT License View
899 All covering indexes Find all covering indexes, which include data from additional columns in leaf blocks. General INFORMATION_SCHEMA+system catalog base tables 2020-12-23 11:54 MIT License View
900 All non-unique indexes Find secondary indexes that have been created in the database. General INFORMATION_SCHEMA+system catalog base tables 2020-12-23 11:50 MIT License View