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
761 ROW level BEFORE triggers with RAISE EXCEPTION but without RETURN NULL Although RAISE EXCEPTION stops the execution it would be a good style to still return. In this case the return should bring back NULL, i.e., the row will not be processed further Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
762 Rules with the same name within the same schema Find names of rules that are used within the same schema more than once. Give different triggers different names. Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View
763 SET DEFAULT compensatory action is unsuitable Find foreign keys with SET DEFAULT compensatory action where the foreign key column does not have a default value. Compensatory actions cannot make changes that violate integrity constraints in a database. SET DEFAULT means that there shoud be a default value at the foreign key column. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
764 SET NULL compensatory action is unsuitable Find foreign key constraints that use a SET NULL compensating action but a foreign key column is mandatory, i.e., does not permit NULLs. Compensatory actions cannot make changes that violate integrity constraints in a database. SET NULL cannot put NULL to a mandatory column (delete a foreign key value). Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
765 Sorting rows based on random values in derived tables without limiting rows Find derived tables (views and materialized views) that sort rows based on random values but do not limit the number of rows. This is unnecessary because without sorting the rows are returned in a unspecified order. Sorting based on random values is a computationally expensive operation. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
766 STATEMENT level triggers and ROW level AFTER triggers without RETURN NULL Write correct code "The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null." (PostgreSQL documentation) Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
767 Table privileges have been granted to PUBLIC You should follow the principle of least privilege and thus not have in your database tables that usage privileges are granted to the pseudo-role PUBLIC, i.e., to all the database users now and in the future. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
768 Table, routine, and usage privileges that have been granted to a superuser Find table, routine, and usage privileges that have been granted to a superuser. Superuser can do anything in the database and thus does not need the privileges. The result is a sign that perhaps the executed GRANT statements were incorrect (wrong username) or the grantee later got superuser status (that it shouldn't have). Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
769 TG_ARGV is missing Write correct code. If you pass arguments to a trigger function, then the function should use the arguments. TG_ARGV[]: "Data type array of text; the arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value." (PostgreSQL documentation) Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
770 Three-valued logic (Boolean columns) Find base table columns that have Boolean type and do not have NOT NULL constraint. Use two-valued logic (TRUE, FALSE) instead of three-valued logic (TRUE, FALSE, UNKNOWN). Because NULL in a Boolean column means unknown make all the Boolean columns mandatory. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
771 Trigger is used to enforce referential integrity Find tables where user-defined (non-system) triggers are used to implement referential integrity. In addition to table name show the triggers and the number of triggers. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
772 Triggers with the same name within the same schema Find names of triggers that are used within the same schema more than once. Give different triggers different names. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
773 Unique index definition instead of a key declaration Find cases where a unique constraint (that is not case-insensitive or partial, i.e., applies only to certain rows) has been enforced by using a CREATE UNIQUE INDEX statement instead declaring a PRIMARY KEY, UNIQUE, or EXCLUDE constraint. You should try to work on as high level of abstraction as possible. According to the ANSI-SPARC Architecture indexes are a part of internal database schema whereas constraints are a part of conceptual schema, i.e., at the higher level of abstraction. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
774 Unnecessary privileges to use trigger functions A user that corresponds to an application does not have to have privileges to use trigger functions. If it has these, then it violates the principle of least privilege. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
775 Unnecessary usage of the numeric type in case of base table columns Find base table columns that have type NUMERIC and the scale is 0, i.e., one can record in the column only integer values. Arithmetic operations are slower in case of the numeric type compared to an integer type. Thus, in order to record integer values, one should use columns with the type SMALLINT, INTEGER, or BIGINT instead of NUMERIC(p,0). Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
776 Unused composite types (for table columns, typed tables, input and output parameters) Find user-defined composite types that are not used in case of any table, column, and routine (input or otput) parameter (as their type). Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code. Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View
777 Unused domains (for base table columns and parameters) Find domains that are not used in case of any base table column and routine (input or otput) parameter (as their type). Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code. Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View
778 Unused enumerated types (for base table columns, domains, and parameters) Find enumerated types that are not used in case of any base table column, domain, and routine (input or otput) parameter (as their type). Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
779 Unused foreign data wrappers Find foreign data wrappers that do not have any associated foreign servers. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
780 Unused foreign servers Find foreign servers that do not have any associated foreign tables. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View