PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 104 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 Age columns in base and foreign tables This query identifies columns in base and foreign tables that are intended to store age values. It detects these fields by analyzing both their naming conventions and data types. Problem detection INFORMATION_SCHEMA only 2026-05-15 14:57 MIT License View
2 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
3 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
4 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
5 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
6 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
7 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
8 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
9 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
10 Base table column of quantities does not have a numeric type (based on column names) Find base table columns that name refers to the possibility that these are used to register quantities of things. Find the columns that do not have a numeric type. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
11 Base table column of sums of money does not have a numeric type (based on column names) Find base table columns that name refers to the possibility that these are used to register prices/sums of money. Find columns that do not have a numeric type. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
12 Base table column of sums of money has too big or small scale Find base table columns that name refers to the possibility that these are used to register data about prices/sums of money. Find the columns that have decimal type but have a too big (bigger than six) or a too small scale (zero). The selection of field size must be precise and should take into account the possible data in the column. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
13 Base table column of surrogate key values does not have an integer data type (based on column names) Find base table columns that belong to a primary key, unique, or foreign key constraint and that name refers to the possibility that these are used to hold surrogate key values. Find the columns where the data type of the column is not an integer type or uuid. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
14 Base table columns for recording geographic coordinates (based on column names) Find base table columns that are according to the name meant for recording coordinates. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
15 Base table columns for recording geographic coordinates that do not have a suitable type (based on column names) Find base table columns that are according to the name meant for recording geographic coordinate but do not have a suitable type (numeric or point). Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
16 Base table columns for storing username, password, and salt Find base table columns that name refers to the possibility that these are used to register usernames, passwords, and salt. Password should not be open text. It should be a hash value that has been hashed by using salt. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
17 Base table columns permitting e-mail addresses without @ sign Find non-foreign key base table columns that name refers to the possibility that these are used to register e-mail addresses. Find the columns that do not have any simple CHECK constraint that contains @ sign. A simple check constraint covers a single column. In this case registration of e-mail addresses without @ is most probably not prohibited. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
18 Base table columns permitting negative prices/quantity Find non-foreign key base table columns that name refers to the possibility that these are used to register prices/quantities. Find the columns that do not have any simple CHECK constraints, i.e., a constraint that covers only this column. In this case registration of negative price/quantity is most probably not prohibited. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
19 Base table columns permitting URLs without a protocol Find non-foreign key base table columns that name refers to the possibility that these are used to register URLs. Find the columns that do not have any simple CHECK constraint that references to a protocol. A simple check constraint covers a single column. In this case registration of URLs without a protocol is most probably not prohibited. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
20 Base table has a national identification number as a key This query identifies base table columns whose names suggest they store national identification numbers (personal codes), specifically targeting those that serve as a primary or unique key. Using a national ID as a standalone key enforces a highly restrictive business rule: it assumes that all individuals in the system come from a single country, as identical ID numbers can exist in different countries. The query flags these instances to prompt a review, ensuring that this single-country limitation is intentional and actually aligns with the domain rules. General INFORMATION_SCHEMA+system catalog base tables 2026-06-01 16:05 MIT License View