PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1036 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 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 2025-11-07 10:11 MIT License View
42 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 2025-11-07 10:11 MIT License View
43 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 2025-11-07 10:11 MIT License View
44 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 2025-11-07 10:11 MIT License View
45 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 2025-11-07 10:11 MIT License View
46 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 2025-11-07 10:11 MIT License View
47 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 2025-11-07 10:11 MIT License View
48 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 2025-11-07 10:11 MIT License View
49 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 2025-11-07 10:11 MIT License View
50 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 2025-11-07 10:11 MIT License View
51 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 2025-11-07 10:11 MIT License View
52 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 2025-11-07 10:11 MIT License View
53 Avoid using length function This query identifies all expressions that use the non-standard length() function. Although length() is a functional synonym for char_length() in PostgreSQL, its use is discouraged for two primary reasons: char_length() is the SQL-standard function, and length() has different semantics in other database systems (e.g., returning byte length in MySQL). To enhance code portability and prevent semantic ambiguity for developers, this query flags all instances of length() to encourage standardization on the char_length() function. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-13 12:53 MIT License View
54 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 2025-11-07 10:11 MIT License View
55 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 2025-11-07 10:11 MIT License View
56 Base table column of measurements does not have a correct data type This query identifies a semantic mismatch in data type selection for columns intended to store measurement data. It targets columns whose names imply a quantitative measurement (e.g., "length", "weight", "count", excluding boolean prefixes like "is_") but are not defined with a numeric data type (INTEGER, NUMERIC, etc.). Storing measurements as text (VARCHAR) prevents mathematical operations, aggregation, and proper sorting, and is considered a design flaw. Problem detection INFORMATION_SCHEMA only 2025-12-03 19:19 MIT License View
57 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 2025-11-07 10:11 MIT License View
58 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 2025-11-07 10:11 MIT License View
59 Base table column of personal names does not restrict the maximum character length This query identifies base table columns that, based on their name, are presumed to store personal names but lack an explicit maximum length constraint. It operates on a heuristic, flagging columns with names like first_name, surname, etc., that are defined with unbounded textual types (e.g., text, varchar) and have no corresponding CHECK constraint to limit their length (e.g., char_length(col) <= n). The absence of such a limit is a design flaw that can introduce usability issues in front-end applications and create potential security vulnerabilities related to excessive data submission. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-13 13:04 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 component (mononymous persons). Database design must take it into account. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View