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
421 Incorrect check of NULLs Find the use of =NULL and <>NULL in case of table level check constraints, domain level check constraints, WHEN clauses of triggers, WHERE clauses of rules, subqueries of derived tables, and bodies of routines. Write correct code. In order to determine as to whether a value is missing or not one has to use the IS [NOT] NULL predicate. NULL is the marker in SQL that denotes a missing value. Although it is often called "NULL value", one cannot treat it as an ordinary value, i.e., use it in comparisons as a value. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-12 12:10 MIT License View
422 Incorrect comparison operator Find PL/pgSQL routines that use comparison operators =< or =>. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-11 14:50 MIT License View
423 Incorrect data type (based on default values) Find columns of base tables that have the default value CURRENT_USER or SESSION_USER but the data type is CHAR or TEXT. Problem detection INFORMATION_SCHEMA only 2023-12-30 11:06 MIT License View
424 Incorrect field size (based on default values) Find columns of base tables that have the default value CURRENT_USER or SESSION_USER but the field size is not 63 (default maximum identifier length in PostgreSQL). Problem detection INFORMATION_SCHEMA only 2023-12-30 11:05 MIT License View
425 Incorrect password hash update Find row level update triggers that incorrectly implement update of password hash. It should not be that the new password hash is calculated based on the existing hash. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
426 Incorrect prefix of a constraint name or an index name If the name of an object has the prefix that refers to the type of the object (for instance, primary key constraint or foreign key constraint), then you should use references to the correct object type. Find prefixes of constraint names and index names that incorrectly refer to the type of the object. For instance, incorrect would be to use chk_ as the prefix of an index name or pk_ as the prefix of a check constraint name. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-28 10:48 MIT License View
427 Incorrect prevention of the empty string or strings that consist of only spaces in a field Find columns of base tables and foreign tables where the requirement that there should not be empty strings or strings that consist of only spaces in the column has been implemented incorrectly - by using the constraint trim(column_name) IS NOT NULL. PostgreSQL (differently from Oracle) does not replace the empty string with NULL. Empty string is a value but NULL is a special marker that denotes a missing value. Thus, in case of such constraint the DBMS checks a proposition '' IS NOT NULL. This is a true proposition and the DBMS does not prevent registration of such a row. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View
428 Incorrect reference to a system-defined function in the routine body Find user-defined routines that possibly use incorrect name of a system-defined function (currenttimestamp (correct is current_timestamp), currentdate (correct is current_date), currenttime (correct is current_time), local_time (correct is localtime), local_timestamp (correct is localtimestamp),localdate (there is no such function),local_date (there is no such function), sessionuser (correct is session_user), ucase (correct is upper), lcase (correct is lower)). The problem can arise only if the routine uses dynamic SQL. In case of static SQL the DBMS checks the SQL statemen at the creation time and finds out that for instance, SELECT Count(*) AS cnt FROM Emp WHERE hiredate<=currentdate; is incorrect statement because currentdate is not a function name and there is no column currentdate in the table Emp. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-10-25 16:48 MIT License View
429 Incorrect specification of logical or in regular expressions Find the use of regular expressions where logical or is incorrectly specified, i.e., (| or |). Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-24 12:07 MIT License View
430 Incorrect suffix of a constraint name or an index name If the name of an object has the suffix that refers to the type of the object (for instance, primary key constraint or foreign key constraint), then you should use references to the correct object type. Find suffixes of constraint names and index names that incorrectly refer to the type of the object. For instance, incorrect would be to use _chk as the suffix of an index name or _pk as the suffix of a check constraint name. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-28 10:44 MIT License View
431 Incorrect use of non-deterministic functions in CHECK constraints Do not create a constraint in case of which data that satisfied a constraint c at the registration time suddenly does not satisfy it any more as the time goes by. Find all the check constraints that use non-deterministic functions (now, current_timestamp, localtimestamp, current_date, current_time) in a way that makes this situation possible. Fort instance, localtimestamp(0)>end_date is an example of such constraint. Problem detection INFORMATION_SCHEMA only 2024-11-22 15:29 MIT License View
432 Index FILLFACTOR is not default Find all indexes where FILLFACTOR is not default, i.e., it has been changed. The default is different in case of different index types is different. In case of B-tree indexes the default is 90. General system catalog base tables only 2024-11-21 09:28 MIT License View
433 INFORMATION_SCHEMA is missing Make sure that you do not drop INFORMATION_SCHEMA schema. In this case most of the design checking queries will not work. This schema automatically exists in all databases. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-12-07 19:07 MIT License View
434 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
435 Input parameters that names do not follow the convention to start with _ or p_ For the sake of making code better understandable follow naming conventions. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
436 Input parameters with the same name have different types Find named input parameters of routines that have the same name but different type. It could be a mistake or deliberate due to overloading of routines. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-01-24 15:01 MIT License View
437 Installed extensions Try to use as much the possibilities of the DBMS as possible. On the other hand, do not install extensions that are not needed in order not to overcomplicate the database. General system catalog base tables only 2020-11-06 14:51 MIT License View
438 Insufficient number of user-defined base tables There must be at least n (seven in this case) user-defined base tables in the database. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
439 Insufficient number of user-defined domains There must be at least n (one in this case) user-defined domains in the database each of that must be used in case of at least two columns of base tables. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
440 Insufficient number of user-defined foreign tables There must be at least n (two in this case) user-defined foreign tables in the database. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View