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
601 Routines with the same name and parameters in different schemas Find user-defined routines with the same name and parameters (including the order of parameters) in different schemas. General INFORMATION_SCHEMA+system catalog base tables 2021-10-25 16:11 MIT License View
602 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
603 Base tables with exactly one key Find all base tables that have exactly one PRIMARY KEY or UNIQUE constraint. Find and enforce all the keys. Are you sure there are not more keys in the table? General system catalog base tables only 2021-10-16 10:39 MIT License View
604 All key constraints Find all the primary key and unique constraints of base tables. General system catalog base tables only 2021-10-16 10:37 MIT License View
605 Primary key columns are not the first in a table In SQL tables each column has the ordinal position. Find all the base tables where the primary key columns are not the first in the table, i.e., there is at least one non-primary key column that comes before a primary key column. It is easier to grasp the primary key if its columns are the first in the table. It could be that a table inherits from an abstract table where no keys have been defined. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-10-16 10:33 MIT License View
606 Definition of a non-minimal superkey instead of a candidate key (based on key constraints) Find primary/key unique constraints (sets of columns) that are proper subsets of other primary key/unique constraints of the same table. Candidate key is a minimal superkey, meaning that it is not possible to remove columns from the candidate key without losing its uniqueness property. One should define primary key's and unique constraints based on candidate keys, i.e., the keys should not have redundancy in terms of columns. Problem detection system catalog base tables only 2021-10-16 10:29 MIT License View
607 Duplicate keys Find completely overlapping key (primary key and unique) constraints. This is a form of duplication. It leads to the creation of multiple indexes to the same set of columns. Problem detection system catalog base tables only 2021-10-16 10:27 MIT License View
608 Base table columns for recording geographic coordinates that do not have a suitable type (based on column names) Find base table columns that are according to the name meant for recording geographic coordinate but do not have a suitable type (numeric or point). Problem detection INFORMATION_SCHEMA only 2021-10-08 12:01 MIT License View
609 Base table columns for recording geographic coordinates (based on column names) Find base table columns that are according to the name meant for recording coordinates. General INFORMATION_SCHEMA only 2021-10-08 11:59 MIT License View
610 Deferrable foreign key constraint with a RESTRICT compensating action Find deferrable foreign key constraint with ON UPDATE RESTRICT or ON DELETE RESTRICT compensating action. Referential actions are carried out before, and are not part of, the checking of a referential constraint. Deferring a referential constraint defers the checking of the of the constraint (a ) but does not defer the referential actions of the referential constraint. In PostgreSQL the essential difference between NO ACTION and RESTRICT is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not. Thus RESTRICT could result with the failure of data modification where in case of NO ACTION the modification would succeed. Problem detection system catalog base tables only 2021-10-08 11:29 MIT License View
611 Deferrable constraints Find all deferrable constraints. General system catalog base tables only 2021-10-08 11:25 MIT License View
612 Initially deferred constraint triggers with unnecessary locking Initially deferred constraint triggers do not need explicit statements for locking tables or rows. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-10-08 11:25 MIT License View
613 Base tables with multiple comment columns Find base tables with more than one comment columns General INFORMATION_SCHEMA only 2021-04-02 12:21 MIT License View
614 Base tables with multiple name columns Find base tables with more than one name columns. Perhaps the normalization level of the table is low. General INFORMATION_SCHEMA only 2021-04-02 12:21 MIT License View
615 Using an internal data type - name Find base table columns that use type name that is used in system catalog tables. It is not a problem if the column is meant for recording identifiers of database objects. General INFORMATION_SCHEMA only 2021-03-30 13:36 MIT License View
616 Inconsistent precision and scale usage in case of registering sums of money Find as to whether different precisions/scales are used in case of registering data about sums of money in different columns. Problem detection INFORMATION_SCHEMA only 2021-03-29 13:07 MIT License View
617 Registration/modification time is not automatically set Find columns of base tables that name and type suggest that the column should contain the row registration time or last modify time but the column does not have a default value. Problem detection INFORMATION_SCHEMA only 2021-03-28 17:36 MIT License View
618 Base table columns with the same name and type have different field sizes Find base table columns that have the same name and type but different field size. Problem detection INFORMATION_SCHEMA only 2021-03-28 16:59 MIT License View
619 Perhaps the type of a base table column should be XML, JSON, or JSONB (based on column names) Find base table columns that name refers to the possibility that these are used to register XML/JSON values. Find the columns that do not have an appropriate data type (xml, json, jsonb). One shouldn't use columns with a textual type to register such data. Problem detection INFORMATION_SCHEMA only 2021-03-28 15:30 MIT License View
620 Inconsistency between the name and the type of a base table column (timestamps) Find base table columns that name refers to the possibility that these are used to register timestamps. Find the columns that do not have an appropriate data type. Column names should reflect the data that is possible to record in the column. For instance, in case of temporal data the column name should indicate as to whether we record dates or timestamps. If the column type is "timestamp", then the suffix of the column name should be "aeg" (Estonian) or "time" (English). Problem detection INFORMATION_SCHEMA only 2021-03-28 15:12 MIT License View