PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1038 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
741 The same database object name is used repeatedly in case the same database object type Find what database object names are used more than once in case the objects of the same type. If the names differ from each other only by digits or underscores, then consider these the same name. For instance, if there are base tables Person and Person2 (in the same schema or different schemas), then the query returns the name Person. Make sure that there is no duplication of implementation elements in the database. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
742 The same name is used in different contexsts Find the names that are used in case of different types of elements Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
743 The shortest names of database objects by object type "Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) Names should be expressive. Find the shortest (identifiers) names of user-defined objects by their type. These could be the first candidates of renaming in order to give to database objects better names. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
744 The SQL-language routines with the body that is string literal Find SQL-language routines that have the body that is string literal, i.e., the body is not SQL-standard function body. Routines with a SQL-standard body are permitted starting from PostgreSQL 14. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
745 The total number of physical lines of code in the bodies of user-defined SQL and PL/pgSQL functions/procedures Find a numerical measure that describes the extent of the database public interface (virtual data layer) through which users access the database. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
746 The usage of data type formatting functions Find expressions that use a data type formatting function - to_char, to_number, to_date, to_timestamp. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
747 The usage of double vs singular underscores or spaces in names as separator of name components Improve the readability of names. Find the number of names (identifiers) of user-defined database objects that contain two or more consecutive underscores or spaces as the separator of name components vs. the number of names that contain a single underscore or space to separate name components. Try to be consistent in the usage of underscores. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
748 The use of xmin hidden column in views and routines Find the number of views and materialized views that have a column with the xid type and the number of routines that contain a UPDATE or a DELETE statement that search condition refers to the xmin column. If one uses optimistic approach for dealing with the concurrent modifications of data, then xmin values should be presented by views and used in routines that modify or delete rows. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
749 Three-valued logic (non-Boolean columns) Find non-foreign key columns of base tables that probably (based on the column name) contain values that represent truth values but do not have NOT NULL constraint. Use two-valued logic (TRUE, FALSE) instead of three-valued logic (TRUE, FALSE, UNKNOWN). Because NULL in a Boolean column means unknown make all the columns mandatory. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
750 Too few rounds in case of calculating the hash Find user-defined routines where gen_salt function is used with the number of rounds that is smaller than 12. The number should be adjusted based on the hardware where the system resides. Password hashing should take at least 250 ms. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
751 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-11-07 10:11 MIT License View
752 Too generic names (columns) (there is a column with a more specific name in the table) Find column names in case of which the same table has another column (with more specific name) that name contains the column name in the end or in the beginning. For instance, a base table has columns parent and root_parent and the former col-umn name is too generic, i.e., it should be more specific. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
753 Too generic names (many-to-many relationship types that do not have additional attributes) This query identifies junction tables with semantically redundant names, specifically targeting those whose names contain the word "join". The structure of a many-to-many table (typically two foreign keys forming a composite primary key) is inherently declarative of its role as a join mechanism. Including the word "join" in the name is superfluous and violates the principle of naming based on the entity or relationship being modeled, not the implementation detail. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-10 09:41 MIT License View
754 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-11-07 10:11 MIT License View
755 Too generic names (parameters) (there is a parameter with a more specific name in the routine) Find parameter names in case of which the same routine has another parameter with the same mode but with more specific name, i.e., the name contains the parameter name in the end or in the beginning. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
756 Too generic names regarding persons and their names (columns of derived tables) Find derived table columns that name refers to the possibility that these contain person names. The names of derived table columns that contain person names should refer to the role that the corresponding entity type has in the view. For instance, if view active_product has a column with the name surname, then the column name does not give information what is the role of the person in the context of the view. Better name would be, for instance, registrator_surname. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
757 Too generic names (routines) "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. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
758 Too generic names (tables) This query identifies tables with semantically weak, generic names that violate schema design best practices. It flags tables with name components such as "table", "data", "information", or "list". The principle is that a table name should accurately represent the real-world entity it models. Using generic nouns obscures the schema's meaning, reduces readability, and forces developers to inspect the table's contents to understand its purpose. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-13 14:10 MIT License View
759 Too many slashes in regular expressions Find patterns of regular expressions where more than \ is written instead of \, e.g., \\s is used instead of \s to refer to a character class. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
760 Too short names of database objects "Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) The names should be meaningful and searchable. Find the names (identifiers) of user-defined database objects that are shorter than three characters. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View