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
41 All user schemas Find all the schemas in the database that are not used for the system purposes. General INFORMATION_SCHEMA only 2022-11-04 15:14 MIT License View
42 All user triggers that are associated with tables Find user-defined triggers that react to data modifications in tables. Triggers should be used only for the tasks that cannot be achieved in a declarative manner, i.e., by declaring a constraint. Triggers of the same table with the same event_manipulation, action_timing, and action_orientation are sorted based on the trigger name. This is the order of execution of triggers. General INFORMATION_SCHEMA+system catalog base tables 2021-01-19 11:27 MIT License View
43 AND takes precedence over OR Make sure that Boolean expressions take into account precedence rules of Boolean operators. AND operator has precedence over OR operator. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
44 A non-parameterized table function instead of a view Find table functions that do not have any parameters. Prefer simpler and more portable solutions. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-03 21:16 MIT License View
45 A predefine character class has been incorrectly specified Find regular expressions where a predefined character class is incorrectly specified, e.g. [digit] instead of [:digit:]. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-23 12:09 MIT License View
46 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
47 Are there enough routines that implement database operations? Find user-defined routines that implement database operations (comment refers to an operation) but show these only if there are at least eight such routines. Contracts of database operations are specified in the system analysis documentation. The contracts apply the idea of design by contract in the field of databases. General INFORMATION_SCHEMA+system catalog base tables 2021-11-03 20:07 MIT License View
48 A routine is invoked only once Find user-defined routines that are invoked by exactly one user-defined routine. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-22 14:53 MIT License View
49 A setter does not update a table Find user-defined non-trigger SQL and PL/pgSQL routines that name starts with "set" (but not with "setting") but do not contain a UPDATE statement. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-27 18:35 MIT License View
50 A state machine is implemented with the help of an enumeration type Find implementations of state machines that uses an enumeration type. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-26 15:39 MIT License View
51 A state machine is implemented with the help of a state classifier table Find implementations of state machines that use a state classifier table. General system catalog base tables only 2023-11-26 15:39 MIT License View
52 A table has the same name as a routine Find table names that are the same as some routine name. Use different names to avoid confusion. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
53 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
54 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
55 Base table column name is the same as its domain name Find base table columns that have the same name as the domain name or the data type name of the column. The names may have different uppercase/lowercase characters. Columns, domains, and types are different concepts in SQL and perhaps it is better to use different names in case of these. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View
56 Base table column of comments/descriptions has an incorrect data type or maximum character length Find base table columns that name refers to the possibility that these are used to register comments/descriptions. Find the columns where the data type is not VARCHAR and TEXT or in case of VARCHAR the maximum number of permitted characters is smaller than 1000. In case of determining field sizes choose a size that permits registration of all possible legal values. Problem detection INFORMATION_SCHEMA only 2022-05-01 13:39 MIT License View
57 Base table column of measurements does not have a correct data type Find base table columns that name refers to the possibility that these are used to register measurement results. Find the columns that do not have an integer or numeric type. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View
58 Base table column of national identification numbers does not have a correct data type Find non-textual base table columns that name refers to the possibility that these are used to register national identification numbers (personal codes). The codes can contain additional symbols to numbers. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View
59 Base table column of national identification numbers has a too short field size Find base table columns with VARCHAR type that name refers to the possibility that these are used to register national identification numbers (personal codes). Find the columns where the field size is shorter than 20. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View
60 Base table column of personal names has questionable properties Find base table columns that name refers to the possibility that these are used to register personal names. Although there are very long personal names the general approach is to register a shortened version of these. Thus, a large field size is not a good idea because it would cause usability and security problems. There are persons who only have one name (mononymous persons). Database design must take it into account. Problem detection INFORMATION_SCHEMA only 2022-10-29 20:35 MIT License View