PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1041 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
661 One true lookup table Find tables that contain all (or most) of the classifier values and tables that refer to these. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
662 Only ID primary key Find base base tables have the simple primary key that contains a column with the (case insensitive) name id and an integer type. In addition, the primary key values are generated automatically by the system by using a sequence generator. In addition the base table must not have any unique constraint. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
663 Only one value permitted in a base table or a foreign table column (based on check constraints) Find columns of base tables or foreign tables in case of which a check constraint on the column permits only one value in the column. The constraint may be correct if it is applied to a column of a subtable that is inherited from the supertable or is used to enfore the rule that the table can have at most one row. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
664 Only one value permitted in a base table or a foreign table column (based on enumeration types) Find columns of base tables or foreign tables in case of which the type of the column permits only one value in the column. The type is an enumeration type that specifies only one value. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
665 Only one value permitted in a non-inherited base table or a foreign table column (based on check constraints) Find columns of base tables or foreign tables in case of which a check constraint on the column permits only one value in the column. Exclude columns that are inherited from a supertable because the constraint may be correct if it is applied to a column of a subtable that is inherited from the supertable. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
666 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 2025-11-07 10:11 MIT License View
667 Optional base table columns that participate in a UNIQUE constraint or index Find optional base table columns that participate in a UNIQUE constraint or index. Each base table has one or more candidate keys. One of these is usually selected to be the primary key, other are called alternate keys. To enforce an alternate key one should define a UNIQUE constraint and determine that all the key columns are mandatory (NOT NULL) just like the primary key columns are mandatory. Make sure that the NOT NULL constraint is not missing on these columns. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
668 Optional columns before mandatory columns Find cases where optional columns are before a mandatory column. Place mandatory columns before optional columns to improve comprehensibility of the table. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
669 Optional composite foreign keys that do not have MATCH FULL specified Find optional composite foreign keys that do not have MATCH FULL specified. Without MATCH FULL the system will permit partial foreign key values Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
670 Optional foreign key columns Find foreign key columns that do not have the NOT NULL constraint. It is better to limit the use of NULLs and optional columns due to the problems that it causes in interpreting the query results, making queries, and enforcing constraints. In addition, one should check as to whether this kind of design is consistent with the multiplicities in the conceptual data model. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
671 Optional non-foreign key base table columns that participate in a UNIQUE constraint or index Find optional base table columns that participate in a UNIQUE constraint or index but do not participate in a foreign key constraint. Each base table has one or more candidate keys. One of these is usually selected to be the primary key, other are called alternate keys. To enforce an alternate key one should define a UNIQUE constraint and determine that all the key columns are mandatory (NOT NULL) just like the primary key columns are mandatory. Make sure that the NOT NULL constraint is not missing on these columns. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
672 Overlapping non-function based indexes that have the same leading column but with different operator class Find non-function based indexes (both unique and non-unique) that have identical first column but the operator class that is used in case of the first column is different. Include unique indexes that support a constraint (primary key, unique, exclude), i.e., these indexes have been automatically created due to the constraint declaration. General system catalog base tables only 2025-11-07 10:11 MIT License View
673 Overlapping non-function based indexes that have the same leading column with the same operator class Find non-function based indexes (both unique and non-unique) that duplicate each other because their first column is identical and the operator class that is used in case of the first column is identical. Include unique indexes that support a constraint (primary key, unique, exclude), i.e., these indexes have been automatically created due to the constraint declaration. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
674 Overloading Make sure that there is genuine overloading instead of duplication or dead code. "In some programming languages, function overloading or method overloading is the ability to create multiple functions of the same name with different implementations." (Wikipedia) In PostgreSQL one can do it automagically by having multiple routines with the same name but different parameters in the same schema. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
675 Overloading may cause runtime error Routines in the same schema that have the same name and that have parameters with different types or different number of parameters are not considered to be in conflict at the creation time. However, if defaults are provided in the definition of parameters, then these routines might be conflict during runtime. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
676 Pairs of base tables that have at least two columns with the same names and data types What are the pairs of base tables that have at least two columns with the same names and data types. The tables might violate the principle of orthogonal design and hence might facilitate uncontrolled data redundancy over different tables. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
677 Pairs of non-key column name and type pairs that have in different base tables a different default value Find non-key base table columns with the same name and type that have different default values. Be consistent. Columns with the same name and type shouldn't probably have different default values in case of different tables. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code) Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
678 Pairs of non-key column name and type pairs that have in some base tables a default value and some cases not Find non-key base table columns with the same name and type that have in some cases a default value and some cases not. Be consistent. Columns with the same name and type should probably either always have a default value in case of different tables or never have a default value in case of different tables. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code) Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
679 Parameter name contains the routine name Find parameters that have the same name as the routine. The names may have different uppercase/lowercase characters. Make sure that the naming style is consistent. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
680 Parameter name is the same as the name of a used column If the name of a routine parameter and the name of a column of a table that is used in the routine are the same, then it makes it more difficult to understand the code. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View