PostgreSQL Query Catalog

Find routines with the same parameters (same name and type) regardless of the order of parameters. Make sure that there is no accidental duplication. The query helps users to group together routines that probably have related tasks.

Find base table columns that use type name that is used in system catalog tables. It is not a problem if the column is meant for recording identifiers of database objects.

Find for each base table the last time when the table has been vacuumed or analyzed (either manually or automatically).

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.

Find user-defined SQL and PL/pgSQL routines that do not return a value although the name suggest that it should return a value (starts with "get").

This query identifies comments on derived tables (views) and routines that are 50 characters or longer and written entirely in uppercase letters. Writing long comments exclusively in capital letters should be avoided, as it significantly reduces readability and is generally perceived as shouting in digital communication.

Find base tables where all the columns have a default value.

Find base tables where all he non-primary key columns are optional. Avoid too many optional columns. You have to be extra careful with NULLs in case of formulating search conditions of data manipulation statements.

Find base tables where all unique keys (sets of columns covered by a unique constraint, or a unique index) have at least one optional column. In this case there can be rows in the table where the values that should identify the row are missing. Because NULL is not a value and is not duplicate of another NULL the, follwing is possible: CREATE TABLE Uniq(a INTEGER NOT NULL,
b INTEGER,
CONSTRAINT ak_uniq UNIQUE (a, b));

INSERT INTO Uniq(a, b) VALUES (1, NULL);
INSERT INTO Uniq(a, b) VALUES (1, NULL);

Find table functions that do not have any parameters. Prefer simpler and more portable solutions.

Find regular expressions where a predefined character class is incorrectly specified, e.g. [digit] instead of [:digit:].

Find user-defined routines that are invoked by exactly one user-defined routine.

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.

Find implementations of state machines that uses an enumeration type.

Find table names that are the same as some routine name. Use different names to avoid confusion.

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.

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.

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.

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.

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.