Catalog of PostgreSQL queries for finding information about a PostgreSQL database and its design problems

AND
AND
AND
ANDFrom where does the query gets its information?
AND
AND

There are 961 queries.

Seq nrNameGoalTypeData sourceLast updateLicense...
401Inconsistent time zone and precision usage in case of registering timesFind as to whether different data types (with and without timezone) and precisions are used in case of registering times in different columns.Problem detectionINFORMATION_SCHEMA only2023-11-04 12:30MIT License
402Inconsistent time zone and precision usage in case of registering timestampsFind as to whether different data types (with and without timezone) and precisions are used in case of registering timestamps in different columns.Problem detectionINFORMATION_SCHEMA only2023-11-04 12:28MIT License
403Inconsistent use of casting syntax in routinesFind as to whether PL/pgSQL routines and SQL routines that do not have SQL-standard body use different syntax for casting (cast function vs :: operator).Problem detectionINFORMATION_SCHEMA+system catalog base tables2023-10-29 11:33MIT License
404Inconsistent use of functions and proceduresFind as to whether the database has both procedures as well as functions that do not return a value (i.e., return VOID). Such routines are generally used to modify data in the database. The support of procedures was added to PostgreSQL 11.Problem detectionINFORMATION_SCHEMA+system catalog base tables2023-10-26 15:05MIT License
405Inconsistent use of gratuitous context in the names of non-foreign key and non-candidate key columnsFind the number of names on base table columns that are not a part of a candidate key and a foreign key and that contain the name of the table. Find the number of names on base table columns that are not a part of a candidate key and a foreign key and that do not contain the name of the table. Make sure that table name is used consistently in the column names.Problem detectionINFORMATION_SCHEMA+system catalog base tables2023-10-26 20:25MIT License
406Inconsistent use of length and char_length functionsFind as to whether both functions length and char_length are used in the database.Problem detectionINFORMATION_SCHEMA+system catalog base tables2023-11-10 14:38MIT License
407Inconsistent use of older and newer join syntax in the subqueries of derived tablesFind as to whether the subqueries of derived tables use both older join syntax (join condition in the where clause) and newer syntax (join condition in the from clause).Problem detectionINFORMATION_SCHEMA+system catalog base tables2024-01-24 13:26MIT License
408Inconsistent use of plural and singular in column names in the context of a relationship (English version)Find foreign key constraints that cover one column in case of which the name of refererenced/referencing column is in plural and the name of referencing/refererenced column is in singular.Problem detectionINFORMATION_SCHEMA+system catalog base tables2023-01-13 14:14MIT License
409Inconsistent use of plural and singular in table names in the context of a relationship (English version)Find foreign key constraints in case of which the name of one of the tables is in plural and the name of another table is in singular.Problem detectionINFORMATION_SCHEMA+system catalog base tables2023-01-13 13:41MIT License
410Inconsistent use of plural and singular in table names in the context of a relationship (Estonian version)Find foreign key constraints in case of which the name of one of the tables is in plural and the name of another table is in singular.Problem detectionINFORMATION_SCHEMA+system catalog base tables2023-01-13 14:21MIT License
411Inconsistent use of session_user and current_user functionsFind as to whether both functions session_user and current_user are used in the database.Problem detectionINFORMATION_SCHEMA+system catalog base tables2023-12-30 11:37MIT License
412Incorrect characterization of a user-defined routine as an "immutable" routineFind immutable routines that contain SELECT statements from a database (including SELECT .. FOR UPDATE), INSERT, UPDATE, DELETE, or LOCK statement. The DBMS should get a correct background information about the behaviour of a routine. Immutable routines should not have side effects and should also not ask data from a database because it could change over time. Immutable routines cannot lock tables or its specific rows.Problem detectionINFORMATION_SCHEMA+system catalog base tables2021-11-05 14:08MIT License
413Incorrect characterization of a user-defined routine as a "stable" routineFind stable routines that contain INSERT, UPDATE, DELETE, SELECT … FOR UPDATE, or LOCK statement. The DBMS should get a correct background information about the behaviour of a routine. Stable routines cannot modify data in a database, lock tables, or its specific rows.Problem detectionINFORMATION_SCHEMA+system catalog base tables2021-11-05 13:59MIT License
414Incorrect check of NULLsFind 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 detectionINFORMATION_SCHEMA+system catalog base tables2023-11-12 12:10MIT License
415Incorrect comparison operatorFind PL/pgSQL routines that use comparison operators =< or =>.Problem detectionINFORMATION_SCHEMA+system catalog base tables2023-12-11 14:50MIT License
416Incorrect 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 detectionINFORMATION_SCHEMA only2023-12-30 11:06MIT License
417Incorrect 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 detectionINFORMATION_SCHEMA only2023-12-30 11:05MIT License
418Incorrect password hash updateFind 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 detectionINFORMATION_SCHEMA+system catalog base tables2021-02-25 17:29MIT License
419Incorrect prefix of a constraint name or an index nameIf 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 detectionINFORMATION_SCHEMA+system catalog base tables2021-11-18 21:08MIT License
420Incorrect prevention of the empty string or strings that consist of only spaces in a fieldFind 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 detectionINFORMATION_SCHEMA only2021-02-25 17:29MIT License