PostgreSQL Query Catalog

A Boolean parameter may be used to determine what task to fulfill. In this case the routine has multiple tasks and does not satisfy the separation of concerns principle.

A common style is to declare in each base table one of the candidate keys as the primary key. All the other candidate keys would be alternate keys that will be enforce with the help of UNIQUE + NOT NULL constraints.

A large routine may have multiple tasks that should be split between multiple routines, each of which has a more focused task. Find the PL/pgSQL routines where the number of physical lines of code is bigger than 40.

A large routine may have multiple tasks that should be split between multiple routines that have a more focused task. Find the SQL routines where the number of statements (logical lines of code) is bigger than 5.

All the keys must be enforced. Find base tables that implement M:N relationship types and that allow multiple relationships of the same type between the same entities.

Although RAISE EXCEPTION stops the execution it would be a good style to still return. In this case the return should bring back NULL, i.e., the row will not be processed further

A surrogate key is a primary key that consist of one column. The values of this column do not have any meaning for the user and the system usually generates the values (integers) automatically. In case of defining a surrogate key in a table it is a common mistake to forget declaring existing natural keys in the table. If a key covers an optional column then it does not prevent duplicate rows where some values are missing and other values are equal. 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);

A user that corresponds to an application does not have to have privileges to use trigger functions. If it has these, then it violates the principle of least privilege.

Automatically defined TG_OP variable in a trigger function has data type text. Its value is a string of INSERT, UPDATE, DELETE, or TRUNCATE telling for which operation the trigger was fired. Find the routines that according to the TG_OP value must react to a certain operation but the routine is not associated with any triggers that are fired by the operation. For instance, the routine specifies reaction to DELETE operation but the routine is not associated with any DELETE trigger.

Avoid creating user-defined routines that have the same name as some extension routine because it may cause confusion.

Avoid creating user-defined routines that have the same name as some system-defined routine because it may cause confusion.

Avoid using IS NOT DISTINCT FROM because it makes the query planner to avoid using an index.

Avoid using IS NOT DISTINCT FROM because it makes the query planner to avoid using an index.

Avoid using NOT IN or <>ALL with a non-correlated subquery in PostgreSQL because the query performance will be very poor.

Avoid using NOT IN or <>ALL with a non-correlated subquery in PostgreSQL because the query performance will be very poor, especially in case of large data sizes.

"Avoid using the same word for two purposes. Using the same term for two different ideas is essentially a pun" (Robert C. Martin, Clean Code) Do not use the Estonian words like 'lisa', 'muuda', 'kustuta' or the corresponding English words 'add', 'delete', 'update' as the names of routines because it would make the code much less understandable. What is the task of the routine f_add? Register people? Register orders? What? Do not refer to the generic concepts like data and information because all routines work with these.

Be precise and do not give impossible privileges.

Be precise and write as little code as possible. Prefer expressions with simple invocations of functions like localtimestamp, current_timestamp, and current_date over expressions like (now())::date. Find table columns that have a default value that casts the type of the returned value of a non-deterministic function (now, localtimestamp, current_timestamp, and current_date).

Candidate key is a minimal superkey, meaning that it is not possible to remove columns from the candidate key without losing its uniqueness property. One should define primary key's and unique constraints based on candidate keys. Find primary key and unique constraints where a proper subset of columns has a check constraint that permits only one value in the column. The candidate key should involve only columns without such constraint.

Candidate key is a minimal superkey, meaning that it is not possible to remove columns from the candidate key without losing its uniqueness property. One should define primary key's and unique constraints based on candidate keys. Find primary key and unique constraints where a proper subset of columns has an enumeration type that permits only one value in the column. The candidate key should involve only columns without such type.