PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 997 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 Names of database objects that perhaps end with a sequence number (aggregate view) Find the number of names (identifiers) of user-defined database objects that end with one or more digit. Names should be informative. Duplicates should be avoided. Digits in names are a possible sign of duplication of database objects or unclear names. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-06-27 13:03 MIT License View
2 Extension routines in the schema "public" Find extensions that routines are in the schema public. Problem detection system catalog base tables only 2025-06-27 13:01 MIT License View
3 Names of database objects that perhaps end with a sequence number Find the names (identifiers) of user-defined database objects that end with one or more digit. Names should be informative. Duplicates should be avoided. Digits in names are a possible sign of duplication of database objects or unclear names. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-05-05 07:08 MIT License View
4 Names of database objects that mix snake_case and camelCase/PascalCase Use consistent style of naming. Prefer snake_case. Regular identifiers are stored in the PostgreSQL system catalog in lowercase. Thus, if you use, for instance the identifier thisIsLongTableName, then, for instance,in the pg_dump result you will see the table name thisislongtablename. If the name in the system catalog is thisIsLongTableName, then it means that the name is a delimited identifier, i.e., case sensitive. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-05-05 07:07 MIT License View
5 Too generic names (table constraints) Find table constraints (constraints that are associated directly with the table) that have too generic names like "key" or the name contain too generic words like "data" (all constraints restrict data in the table), or the name is an abbreviation of a constraint type name. Problem detection system catalog base tables only 2025-05-05 07:02 MIT License View
6 Pairs of non-key column name and type pairs that have in different base tables a different default value Find non-key base table columns with the same name and type that have different default values. Be consistent. Columns with the same name and type shouldn't probably have different default values in case of different tables. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code) Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-05-03 14:06 MIT License View
7 Stating the obvious Find database objects that name contains words "data" or "info". These are noise words because databases are meant for storing and manipulating data/information. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-05-03 14:01 MIT License View
8 Too generic names (parameters) Find the names of parameters that are too generic. The routines work with data/information. Thus, it is not a good style to use generic words like data, information, parameter, etc. In the names of parameters. Moreover, avoid too generic parameter names like: id, tyyp, kood, aeg, kp,type, code, time, date, fk, pk, param. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-05-03 13:54 MIT License View
9 The generic names (columns) (aggregate view) Find the too generic column names and the number of their occurrences. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-05-03 13:53 MIT License View
10 Too generic names (columns) Find the names of columns that are too generic. In SQL databases data/information is represented as values in columns. It is not a good style to use generic words like data, information, column, etc. In the names of columns. Moreover, avoid too generic column names like: id, tyyp, kood, aeg, kp,type, code, time, date, fk, pk. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-05-03 13:51 MIT License View
11 CHECK constraints with unnecessary CHECK of NULLs Find CHECK constraints that unnecessarily check that a value is missing. In a CHECK constraint, it is not necessary to check whether a value is NULL, because a CHECK constraint rejects rows where the condition evaluates to FALSE and allows rows where the condition evaluates to TRUE or UNKNOWN. If a value is missing (i.e., NULL), the result of the check is UNKNOWN. Therefore, for example, CHECK (price IS NULL OR price > 0) is equivalent to CHECK (price > 0). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-05-03 13:24 MIT License View
12 SQL routines that return the value of an input parameter Find SQL routines that return the value of an input parameter. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-01-20 14:23 MIT License View
13 Check as to wheteher the names of columns are in the plural or in the singular form (Estonian version) Check as to wheteher the names of tables are in the plural or in the singular form. Make sure that you are consistent in naming. General INFORMATION_SCHEMA+system catalog base tables 2025-01-15 14:20 MIT License View
14 Check as to wheteher the names of parameters are in the plural or in the singular form (Estonian version) Check as to wheteher the names of routine parameters are in the plural or in the singular form. Make sure that you are consistent in naming. General INFORMATION_SCHEMA+system catalog base tables 2025-01-15 14:19 MIT License View
15 Inconsistent use of plural and singular in table names in the context of a relationship (Estonian version) Find foreign key constraints in case of which the name of one of the tables is in plural and the name of another table is in singular. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-01-15 14:18 MIT License View
16 Duplicate removal of duplicates in derived tables Find derived tables (views and materialized views) that contain both DISTINCT and GROUP BY. Make sure that the means for removing duplicate rows from the query result are not duplicated. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-01-15 14:16 MIT License View
17 ROW level BEFORE DELETE and INSTEAD OF DELETE triggers that procedures refer to the row variable NEW Do not write incorrect code. Variable NEW: "Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations." (PostgreSQL documentation) Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-28 11:31 MIT License View
18 ROW level BEFORE INSERT and INSTEAD OF INSERT triggers that procedures refer to the row variable OLD Do not write incorrect code. Variable OLD: "Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is null in statement-level triggers and for INSERT operations." (PostgreSQL documentation) Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-28 11:27 MIT License View
19 Incorrect prefix of a constraint name or an index name If the name of an object has the prefix 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 prefixes 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 prefix of an index name or pk_ as the prefix of a check constraint name. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-28 10:48 MIT License View
20 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 2024-12-28 10:44 MIT License View