PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1022 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
621 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
622 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
623 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
624 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
625 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
626 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
627 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
628 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
629 Parameter name is the same as the name of a used column (ver 2) 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
630 Parameter name is the same as 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
631 Password is unique Find columns that potentially contains passwords and that participate in a unique constraint or index Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
632 Password should not be open text Find base table columns that name refers to the possibility that these are used to register passwords. Find the columns that have a CHECK constraint that seems to determine the minimal permitted length of the values in the column. Passwords in a database table must be hashed and salted. Checking the strength of the password by using a check constraint is in this case impossible and the check constraints that try to do it should be removed from the database. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
633 Perahaps Coalesce invocation is missing or Concat should be used Find user-defined routines and derived tables (views/materialized views) that have a subquery that invokes || operator but does not use Coalesce function to ensure that the arguments are not NULL. In PostgreSQL expression value || NULL returns NULL. In order to get value as the result, one has to replace NULL with a value (empty string) by using, for instance, Coalesce function (an alternative is to use a CASE expression). Instead of || + Coalesce, one could use Concat, Concat_ws, or Format functions for the concatenation. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
634 Perhaps 0 instead of o Find the names of database objects where 0 sign is perhaps used instead of o. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
635 Perhaps a CHECK constraint about required personal name components is missing Find base tables that have optional columns for recording both given name and surname and do not have a CHECK constraint that requires that at least one of the name components must be registered in case of each person. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
636 Perhaps a CHECK constraint about the combination of truth values is missing Find base tables that have at least two columns that have Boolean type and have at least one Boolean column that is not covered by a CHECK constraint involving more than one Boolean column. The Boolean columns possibly mean that we want to record data about states. Often the states depend on each other. For instance, if an order is archived it must be inactive. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
637 Perhaps a CHECK constraint about the order of events is missing Find base tables that have at least two columns that have DATE or TIMESTAMP (with or without time zone) type and do not have any associated CHECK constraint that involves two or more of these columns. The columns mean that we want to record data about events or processes, which often have a certain order. Hence, in case of each row of such a table the values in these columns must be in a certain order. For instance, the end of a meeting cannot be earlier than the beginning of the meeting. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
638 Perhaps an existing domain could be used to define the properties of a base table column? Find non-foreifgn key base table columns that have not been defined based on a domain but that have the same properties (data type, field size, default value, and pemisson to use NULLs) as some domain. If you define a domain, then you should try to use it in case of multiple columns. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
639 Perhaps an inconsistent use of NO ACTION and RESTRICT in the foreign key declarations Find as to whether in case of foreign key constraints both the compensating actions RESTRICT and NO ACTION are used within the same database. If the same thing has to do in different places, then try to do it in the same way. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
640 Perhaps an overcomplicated constraint expression that compares the result of a Boolean expression with a Boolean value Find table and domain CHECK constraints that compare the result of a Boolean expression with a Boolean value. If you can choose between two logically equivalent Boolean expressions choose the more simple expression. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View