PostgreSQL Design Check Queries

Catalog of PostgreSQL queries for finding information about a PostgreSQL database and its design problems

	Choose collection:
AND	Choose query type:
AND	Choose query reliability:
AND	Choose category:	Queries of this catergory provide information about the use of regular expressions.
AND	Choose data source:	From where does the query gets its information?
AND	Enter string:
AND	Has fixing 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.

There are 29 queries.

Seq nr	Name▲	Goal	Type	Data source	Last update	License	...
1	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
2	CHECK constraint with pattern matching on non-textual columns	Find base table and foreign table columns that do not have a textual type but have a single-column check constraint that uses pattern matching. The use of a regular expression, a LIKE clause, or a SIMILAR TO clause in order to constrain values in a non-textual column points to the incorrect selection of operator or column data type.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View
3	Double checking of the maximum character length	Do not duplicate code. In this case a CHECK constraint duplicates the restriction that is already enforced with the help of the declaration of the maximum field size (for instance, VARCHAR(100)).	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-18 13:27	MIT License	View
4	Duplicate specification of character classes	Find regular expressions where within the same specification of a character class the character class alnum as well as 0-9, \d, A-Z, or a-z has been defined.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-12-24 10:43	MIT License	View
5	Duplication of case insensitivity specification in a regular expression	Find regular expressions that use both case insensitive search operator ~* and case insensitivity modifier (?i).	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 12:49	MIT License	View
6	Inconsistent referencing to character classes (digits)	Find as to whether different syntaxes (e.g., 0-9 vs [[:digit:]] or \d) are used to refer to the character class of digits within the same database.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-01 11:57	MIT License	View
7	Inconsistent referencing to character classes (shorthand vs long name)	Find as to whether different syntaxes (e.g., \s vs [[:space:]]) are used to refer to character classes within the same database.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-11-19 11:24	MIT License	View
8	Inconsistent referencing to character classes (shorthand vs long name) (2)	Find as to whether different syntaxes (e.g., \w vs [[:alnum:]]) are used to refer to alphanumeric characters within the database.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-11-19 11:32	MIT License	View
9	Incorrect specification of logical or in regular expressions	Find the use of regular expressions where logical or is incorrectly specified, i.e., (\| or \|).	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-24 12:07	MIT License	View
10	Incorrect suffix of a constraint name or an index name	If the name of an object has the suffix that refers to the type of the object (for instance, primary key constraint or foreign key constraint), then you should use references to the correct object type. Find suffixes of constraint names and index names that incorrectly refer to the type of the object. For instance, incorrect would be to use _chk as the suffix of an index name or _pk as the suffix of a check constraint name.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View
11	Invalid character class	PostgreSQL regular expressions do not have character classes word and letter.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 12:50	MIT License	View
12	Invalid use of the case insensitive search modifier in regular expressions	Find regular expression patterns that use (?i) modifier in any other place than at the beginning of the pattern or (?-i) in any place of the pattern. Such use of the modifiers is not supported by PostgreSQL.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 16:06	MIT License	View
13	LIKE instead of =	Find expressions that use LIKE predicate for precise comparison.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-09 10:43	MIT License	View
14	LIKE with a regular expression pattern	Find expressions that use LIKE (including ILIKE) predicate with a regular expression patterns. In a LIKE pattern one can use only _ and % metasymbols to construct a pattern.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-10-21 10:38	MIT License	View
15	Names of character classes are not in the lowercase	Find regular expressions where the names of character classes are not completely in lowercase. For instance, incorrect is to write [[:UPPER:]] or [[:Upper:]] and correct is [[:upper:]].	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 13:22	MIT License	View
16	Non-predefined character classes must not be between double square brackets	Write correct regular expressions. For instance, if there is a rule that code must consist of one or more digits, then correct expression is code~'^[0-9]+$', not code~'^[[0-9]]+$'.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-13 12:32	MIT License	View
17	Perhaps a regular expression could be simplified	Find regular expressions that name character classes a-zA-Z.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 16:19	MIT License	View
18	Perhaps brackets are missing in a regular expression that uses OR logical operation	Find regular expressions where choice between alternatives has no brackets. Thus, instead of '(a\|b\|c)' there is 'a\|b\|c'. An example: '^a\|b\|c$' -permits in the string symbol "\|" but '^(a\|b\|c)$' does not permit in the string symbol "\|".	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-09 20:22	MIT License	View
19	Perhaps character class [[:alnum:]] could be used	Perhaps regular expressions that refer to character classes alnum and digit could be simplified.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-11-02 09:32	MIT License	View
20	Perhaps checking of file extension is incorrect	Find check constraints of tables that use a regular expression to check as to whether a registered string ends with an appropriate file extension. However, the expression does not put the dot sign into the square brackets nor does have the escape character \before it, i.e., it is interpreted as a single character not as the dot sign in the expression. In regular expressions the dot (.) matches any single character except the newline character.	Problem detection	INFORMATION_SCHEMA only	2023-11-08 15:29	MIT License	View