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
901 Derived tables with sorting Find derived tables where the rows are ordered, i.e., there is ORDER BY clause at the end of the view. Different users may want to see the rows in different order. Thus, the DBMS may have to do extra and unnecessary work by firstly sorting based on one set of rows and after that based on other set of rows. Use sorting if you know that all the users want the rows in the same order and cannot/will not change the order in the application. General INFORMATION_SCHEMA+system catalog base tables 2020-12-22 21:34 MIT License View
902 Different search paths of SECURITY DEFINER functions Find the different search paths used in case of SECURITY DEFINER functions and the number of their occurrences. Make sure that these have been specified correctly and consistently and that they do not refer to any non-existent schemas. General INFORMATION_SCHEMA+system catalog base tables 2020-12-16 10:59 MIT License View
903 Set operations that do not remove duplicate rows in derived tables Find derived tables (views and materialized views) that use a set theoretic operation (union, except or intersect) in a manner that does not remove duplicate rows and thus can produce a multiset not a set. Make sure that it is what is needed. General INFORMATION_SCHEMA+system catalog base tables 2020-12-10 13:32 MIT License View
904 Derived tables with multiple DISTINCT's Find derived tables (views and materialized views) that contain more than one DISTINCT invocation. Make sure that the query is correctly written, including that it does not have unwanted formation of a Cartesian product. General INFORMATION_SCHEMA+system catalog base tables 2020-12-10 13:25 MIT License View
905 Mandatory non-primary key columns Find mandatory non-primary key columns, i.e., the columns that have NOT NULL constraint. General INFORMATION_SCHEMA+system catalog base tables 2020-12-05 19:35 MIT License View
906 Constraints that are redefined in a subtable. Find primary key, unique, foreign key, and exclude constraints that have been defined in a supertable (parent table) and have been redefined in its subtable. General system catalog base tables only 2020-12-02 17:28 MIT License View
907 Different data types that are used to define base table columns Find the number of different data types that are used to define base table columns as well as list all the different types. Data type constrains values in a column. Thus, in case there is a small number of used types, it raises a question as to whether the types have been optimally selected. Sofware measure INFORMATION_SCHEMA only 2020-11-27 10:40 MIT License View
908 Optional base table columns Find optional base table columns, i.e., columns that permit NULLs. Are you sure you want to allow NULLs in these columns? General INFORMATION_SCHEMA only 2020-11-21 03:02 MIT License View
909 Find all non-foreign key columns of base tables Find all non-foreign key columns of base tables. Make sure that no foreign key constraint is missing. General INFORMATION_SCHEMA+system catalog base tables 2020-11-21 02:59 MIT License View
910 Columns with array or user-defined type Find columns with an array or a user-defined type. Each columns should have the most appropriate data type. General INFORMATION_SCHEMA+system catalog base tables 2020-11-19 17:04 MIT License View
911 Columns with JSON, JSONB, or XML type Find columns with JSON, JSONB, or XML type. Each columns should have the most appropriate data type. General INFORMATION_SCHEMA+system catalog base tables 2020-11-19 17:01 MIT License View
912 Columns with BYTEA or OID type Find columns with BYTEA or OID type. These columns are potentially meant for storing large objects. Each columns should have the most appropriate data type. General INFORMATION_SCHEMA+system catalog base tables 2020-11-19 17:00 MIT License View
913 The number of base table columns based on data type Find the number of base table columns based on data type. Sofware measure INFORMATION_SCHEMA only 2020-11-19 15:30 MIT License View
914 Table constraints with the cardinality bigger than one Find constraints that involve more than one columns. Check as to whether the names follow a common style or not. General system catalog base tables only 2020-11-19 13:34 MIT License View
915 The number of table constraints with the cardinality bigger than one Find the number of table constraints with the cardinality bigger than one. Find the number, based on the cardinality, constraint type, and cardinality+constraint type. Sofware measure system catalog base tables only 2020-11-19 13:29 MIT License View
916 Find all publications Find publications of tables that have been created in order to enable logical replication. General INFORMATION_SCHEMA+system catalog base tables 2020-11-18 15:09 MIT License View
917 Foreign keys with ON DELETE CASCADE Find referential constraints (foreign key constraints) that employ ON DELETE CASCADE compensatory action. ON DELETE CASCADE should only be used if it has been created based on a generalization or a composition in the conceptual data model or if the foreign key connects a table that corresponds to the main entity type with a table that corresponds to a non-main entity type. General system catalog base tables only 2020-11-16 10:15 MIT License View
918 Foreign key columns that do not have an integer or varchar type Find foreign key columns that do not have smallint, integer, bigint, or varchar(n) type. These are the most commonly used types in case of key/foreign key columns. Although the use of other types would be perfectly legal as well, make sure that you have selected the best possible data type for each and every column. General INFORMATION_SCHEMA+system catalog base tables 2020-11-16 10:14 MIT License View
919 The number of compensating actions of foreign key constraints Find the number of compensating actions that are specified in case of foreign key constraints. Sofware measure system catalog base tables only 2020-11-16 10:11 MIT License View
920 Foreign keys with ON UPDATE CASCADE Referential constraints (foreign key constraints) that employ ON UPDATE CASCADE compensatory action. ON UPDATE CASCADE should only be used if the referenced key is a natural key (its values can be changed). General system catalog base tables only 2020-11-15 15:39 MIT License View