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
141 Names of columns with the type BOOLEAN The naming of BOOLEAN columns must be consistent. For the better readability the names of such columns could have prefix "is_" (in English) or "on_" (in Estonian) General INFORMATION_SCHEMA+system catalog base tables 2024-01-03 09:41 MIT License View
142 Names of database objects that are used to manage the state of main objects in the database "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 naming must be consistent. One should avoid mixing synonyms like "seisund", "staatus", and "olek" in Estonian or "state" and "status" in English and stick with one term. General INFORMATION_SCHEMA+system catalog base tables 2024-12-21 17:23 MIT License View
143 Names of database objects that contain a digit Find the names (identifiers) of user-defined database objects that contain at least one digit. Names should be informative. Duplicates should be avoided. Digits in names are a possible sign of duplication of database objects or unclear names. General INFORMATION_SCHEMA+system catalog base tables 2024-12-21 16:45 MIT License View
144 Names of database objects that start with an underscore Improve the readability of names. Find the names (identifiers) of user-defined database objects that start with an underscore. This is not necessarily a mistake. For instance, parameter names could start with an underscore. On the other hand, it could be that the prefix is missing in the name. General INFORMATION_SCHEMA+system catalog base tables 2024-12-21 17:23 MIT License View
145 Names of the password columns Find names of columns of base tables, views, and materialized views that contain passwords. Make sure that the naming is consistent, General INFORMATION_SCHEMA+system catalog base tables 2022-11-30 15:04 MIT License View
146 Names of triggers and rules that do contain the table name The names should contain table name in order to make the names better understandable. General INFORMATION_SCHEMA+system catalog base tables 2023-01-13 19:23 MIT License View
147 Naming of declarative base table constraints Find the names of declarative base table constraints. Naming of constraints must be consistent. For instance, do not mix system-defined and user-defined names. General INFORMATION_SCHEMA+system catalog base tables 2021-11-17 20:11 MIT License View
148 Naming of tables Find the names of different types (base table, foreign table, view, materialized view) of tables. Naming of tables must be consistent. For instance, do not mix names in plural and singular form within the same table type. General INFORMATION_SCHEMA+system catalog base tables 2021-11-17 20:13 MIT License View
149 Not inherited CHECK constraints Find CHECK constraints that have been defined in a supertable (parent table) but not in its subtables. An entity that belongs to a subtype should also belong to its supertype. If a subtype entity satisfies some constraint, then logically it must also satisfy the constraints of the supertype as well. If CHECK constraints are not inherited, then this is not guaranteed. If you implement subtyping not merely reuse implementation in the subtables, then the subtables must have at least the same CHECK constraints as the supertable. CHECK(false) on a supertable is an appropriate CHECK constraint if one wants to prevent registering data directly to the supertable, i.e., data can only be added to the subtables. General system catalog base tables only 2020-12-27 17:42 MIT License View
150 NOT NULL domains Find domains with NOT NULL constraints and base table columns that have been defined based on the domain. PostgreSQL CREATE DOMAIN statement documentation points out that it is possible to add NULL's to columns that have a NOT NULL domain and thus suggests to associate NOT NULL constraints with a column instead of the domain. However, this is a non-standard behavior and defeats the idea of domain as a reusable asset. The scenarios where NULLs can appear in columns with a NOT NULL domain are quite exotic and probably cannot appear in production environments. General INFORMATION_SCHEMA+system catalog base tables 2023-10-20 19:10 MIT License View
151 Number of rows in base tables Find the number of rows in base tables. General system catalog base tables only 2022-10-27 19:44 MIT License View
152 One-to-one relationships Find one-to-one relationships between base tables. In this case the foreign key columns must have primary key or unique constraint. These tables could implement inheritance hierarchy that has been specified in the conceptual data model. General system catalog base tables only 2023-01-06 13:39 MIT License View
153 Only one value permitted in a base table or a foreign table column (based on check constraints) Find columns of base tables or foreign tables in case of which a check constraint on the column permits only one value in the column. The constraint may be correct if it is applied to a column of a subtable that is inherited from the supertable or is used to enfore the rule that the table can have at most one row. General INFORMATION_SCHEMA+system catalog base tables 2024-04-30 20:14 MIT License View
154 Optional base table columns Find optional base table columns, i.e., columns that permit NULLs. Are you sure you want to allow NULLs in these columns? General INFORMATION_SCHEMA only 2020-11-21 03:02 MIT License View
155 Optional foreign key columns Find foreign key columns that do not have the NOT NULL constraint. It is better to limit the use of NULLs and optional columns due to the problems that it causes in interpreting the query results, making queries, and enforcing constraints. In addition, one should check as to whether this kind of design is consistent with the multiplicities in the conceptual data model. General INFORMATION_SCHEMA only 2023-11-17 18:36 MIT License View
156 Overlapping non-function based indexes that have the same leading column but with different operator class Find non-function based indexes (both unique and non-unique) that have identical first column but the operator class that is used in case of the first column is different. Include unique indexes that support a constraint (primary key, unique, exclude), i.e., these indexes have been automatically created due to the constraint declaration. General system catalog base tables only 2023-10-28 15:05 MIT License View
157 Overloading Make sure that there is genuine overloading instead of duplication or dead code. "In some programming languages, function overloading or method overloading is the ability to create multiple functions of the same name with different implementations." (Wikipedia) In PostgreSQL one can do it automagically by having multiple routines with the same name but different parameters in the same schema. General INFORMATION_SCHEMA+system catalog base tables 2021-12-20 11:40 MIT License View
158 Paramtetes with an array type, XML, JSON, or JSONB type Find parameters of user-defined routines that type is an array type, xml, json, or jsonb type. Make sure that the parameter name matches the type (perhaps should be in plural). General INFORMATION_SCHEMA+system catalog base tables 2023-01-14 20:21 MIT License View
159 Partial or case insensitive unique indexes Find partial or case insensitive unique indexes. These implement uniqueness constraints that are impossible to enforce with the help of SQL's regular UNIQUE constraint. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
160 Patterns of the Boolean expressions of simple CHECK constraints Find patterns of the Boolean expressions of simple CHECK constraints (involve only one column). Do not solve the same task in different places differently. The same rule could be implemented with CHECK constraints that have different Boolean expressions. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code) General INFORMATION_SCHEMA+system catalog base tables 2022-11-13 16:06 MIT License View