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
701 CHECK constraints with IS NULL Find CHECK constraints to one column (associated with a base table directly or through domain) that check that the value is missing (IS NULL). Write as simple constraint definitions as possible. By default columns are optional, i.e., they permit NULLs. NULL in a column means that checking of a CHECK constraint on the column results with UNKNOWN. CHECK constraints permit rows in case of which checking results with TRUE or UNKNOWN. In case of a CHECK constraint there is no need to check separately that a value in the column could be missing, i.e., be NULL. Thus, for instance, instead of writing CHECK (price>0 OR price IS NULL) write CHECK (price>0). Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
702 Completely overlapping foreign keys Find completely overlapping foreign keys, i.e., the same set of columns of a table is covered by more than one foreign key constraint. These constraints could refer to the same table/key or different tables/keys. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
703 Constraints that are not redefined in a subtable and there is no 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. Additional condition is that in case of the subtable there is no 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) Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
704 Cycle in a hierarchy There should not be cycles in hierarchies meaning that the parent must always be specified. In this case a parent must reference to a child or to itself, otherwise it cannot be registered. Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View
705 Database object that belong to the public interface (virtual data layer) and that names contain the letters õäöüÕÄÖÜ Find database object that belong to the database public interface (virtual data layer - consists of routines and derived tables) and that names contain the letters õäöüÕÄÖÜ (Estonian letters with a diacritic). These letters belong to the Estonian alphabet but do not belong to the ASCII character set. Although permitted by the DBMS, such letters might make it more difficult to use the interface by other programs. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
706 Database object that do not belong to the public interface (virtual data layer) and that names contain the letters õäöüÕÄÖÜ Find database object that do not belong to the database public interface (virtual data layer - consists of routines and derived tables) and that names contain the letters õäöüÕÄÖÜ (Estonian letters with a diacritic). These letters belong to the Estonian alphabet but do not belong to the ASCII character set. Although permitted by the DBMS, such letters might make the naming style inconsistent with the naming style of elements that belong to the public interface. If applications access base tables directly, then the letters can cause the same problems as in case of derived tables, i.e., applications may have difficulties with such names. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
707 Declaratively partitioned tables without partitions Find declaratively partitioned tables that do not have any partitions. Declarative partitioning is implemented in PostgreSQL starting from PostgreSQL 10. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
708 Default is NULL Find all tables where default value is NULL. NULL is the marker that denotes missing value. Implicitly all columns that do not have a default value have the default NULL. There is no need to specify such default value. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
709 Default should be declared at the level of domain not at the level of base table columns Find domains that have been used to define one or more base table non-foreign key columns and all the columns have the same default value that is associated directly with the column not with the domain. Write as little code as possible. If possible, move things "before the brackets" so to say. In this case it means declaring the default value at the level of the domain and not at the level of base table columns. An exception is when the domain is used to define foreign key columns. In this case, it would be appropriate to define the default value at the column level (because different foreign keys could have different default values). Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
710 Disabled user triggers Identify disabled triggers. These should be enabled or dropped, otherwise these are dead code. Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View
711 Domain CHECK constraints with the same name Find domain check constraint names that are used more than once (within the same schema or in different schemas). Different things should have different names. However, here different constraints have the same name. Also make sure that this is not a sign of duplication of domains. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
712 Domain name and type name are the same Use different names to avoid confusion. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
713 Domains with the same name in different schemas Domains are like words that can be used to construct generalized claims about the real world (table predicates). Better not to duplicate the words in the dictionary. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
714 Duplicate CHECK constraints that are connected directly to a table The same table should not have multiple CHECK constraints with exactly the same Boolean expression. 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
715 Duplicate CHECK constraints that are connected to a domain The same domain should not have multiple CHECK constraints with exactly the same Boolean expression. 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
716 Duplicate DEFAULT values of base table columns Find base table columns that have both default value determined through a domain and default value that is directly attached to the column. Do not duplicate specifications of default values to avoid confusion and surprises. If column and domain both have a default value, then in case of inserting data the default value that is associated directly with the column is used. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
717 Duplicate enumerated types Find enumerated types with exactly the same values. There should not be multiple types that have the same values. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
718 Duplicate foreign key constraints Find duplicate foreign key constraints, which involve the same columns and refer to the same set of columns. Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View
719 Duplicate materialized views Find materialized views with exactly the same subquery. There should not be multiple materialized views with 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 system catalog base tables only 2021-02-25 17:30 MIT License View
720 Duplicate NOT NULL constraints Find columns that have NOT NULL constraint through a domain and also directly. Do not duplicate NOT NULL constraints in orde to avoid confusion and surprises. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View