PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 13 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
1 Are the passwords hashed? Find base table columns that name refers to the possibility that these are used to register passwords. Return a value from each such column. Make sure that the password is not registered as open text. General INFORMATION_SCHEMA+system catalog base tables 2020-11-10 12:14 MIT License View
2 At most one row is permitted in a table (based on check constraints) Find base tables and foreign tables where based on a check constraint, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a check constraint permits only one possible value in a column, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint. General INFORMATION_SCHEMA+system catalog base tables 2022-11-03 15:21 MIT License View
3 At most one row is permitted in a table (based on enumeration types) Find base tables and foreign tables where based on the type of a column, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a column has an enumeration type with exactly one value, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint. General INFORMATION_SCHEMA+system catalog base tables 2022-11-03 15:18 MIT License View
4 Base tables with plenty of data Find base tables that have 1000 rows or more. General system catalog base tables only 2022-10-21 11:25 MIT License View
5 Base tables with the biggest number of rows Find the base tables that belong to the top 5 in terms of the number of rows in the table. There should be test data in the tables. General system catalog base tables only 2022-10-21 01:48 MIT License View
6 Columns with only one value Find base table columns that contain only one value. Perhaps it is an unnecessary column. Having only one value is most likely inadequate for testing. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
7 Do not assume you must use files (based on user data) Find cases where you store images and other media as files outside the database and store in the database only paths to the files. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-03-10 12:58 MIT License View
8 Do not format comma-separated lists (based on user data) Find, based on the data that users have recoreded in a database, cases where a multi-valued attribute in a conceptual data model is implemented as a textual column of a base table. Expected values in the column are strings that contain attribute values, separated by commas or other separation characters. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-03-12 15:02 MIT License View
9 Empty columns Find columns in non-empty tables that do not contain any values. If there are no values in a columns, then it may mean that one hasn't tested constraints that have been declared to the column or implemented by using triggers. It could also mean that such columns are not needed at all. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
10 Empty tables Find base tables where the number of rows is zero. If there are no rows in a table, then it may mean that one hasn't tested constraints that have been declared to the table or implemented by using triggers. It could also mean that the table is not needed because there is no data that should be registered in the table. Problem detection system catalog base tables only 2022-10-21 15:55 MIT License View
11 Foreign key column has a default value that is not present in the parent table Find foreign key columns that have a default value that is not present in the parent table. Identify default values that cause violations of the referential constraints. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
12 Number of rows in base tables Find the number of rows in base tables. General system catalog base tables only 2022-10-27 19:44 MIT License View
13 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 or maximal 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 2021-02-25 17:29 MIT License View