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
421 Column name contains the table name Find columns that have the same name as the table. The names may have different uppercase/lowercase characters. Make sure that the naming style is consistent. General INFORMATION_SCHEMA+system catalog base tables 2023-01-06 12:38 MIT License View
422 Column name is the same as the table name Find columns that have the same name as the table. The names may have different uppercase/lowercase characters. Sometimes columns with such names are used as the key columns. Make sure that the naming style is consistent. General INFORMATION_SCHEMA+system catalog base tables 2023-01-06 12:38 MIT License View
423 Table check constraints with regular expressions Find all CHECK constraints (except NOT NULL) that are associated with a base table or a foreign table column and use a regular expression. It is useful to enforce as many constraints at database level as possible. In this way one improves data quality as well as gives extra information to the database users (including the DBMS engines, development environments, and applications). General INFORMATION_SCHEMA only 2022-12-13 12:47 MIT License View
424 Too wide derived (dependent) table Find derived tables (views, materialized views) that are based on more than five tables and that have more than 15 columns. This view might produce "a denormalized world view" where all the data is together in one table and applications make queries based on this single view to fulfill their specific tasks. Such view does not follow the separation of concerns principle. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-12-13 12:34 MIT License View
425 FOR UPDATE in derived tables Find derived tables that subquery uses FOR UPDATE construct. Reading a data element shouldn't block other read operations of the same element. Thus, you shouldn't use exclusive locking command in a view. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-12-12 11:11 MIT License View
426 ROW level BEFORE UPDATE triggers that do not return the new row Find row level BEFORE UPDATE triggers that do not return the new row version. Exclude triggers that raise WARNING/EXCEPTION. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-12-08 15:59 MIT License View
427 CHECK constraints that perhaps incorrectly consider 'infinity' and '-infinity' special values Such special values belong to the types DATE, TIMESTAMP, NUMERIC, REAL, and DOUBLE PRECISION. No value can be bigger than infinity or smaller than -infinity. If the check constraint cheks that a value must be bigger than -infinity or smaller than infinity, then it does not restrict (almost) anything. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-12-07 20:13 MIT License View
428 INFORMATION_SCHEMA is missing Make sure that you do not drop INFORMATION_SCHEMA schema. In this case most of the design checking queries will not work. This schema automatically exists in all databases. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-12-07 19:07 MIT License View
429 The number of SQL-language routines with different types of bodies Find for each user schema that contain a SQL-language routine how many of these have the body that is string literal and how many have the body (possible starting from PostgreSQL 14) that is parsed at the routine definition time. Try to be consistent, i.e., use the same solution in case of all the routines. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2022-12-02 11:33 MIT License View
430 Perhaps primary key columns could be renamed Find the names of simple primary key columns that name does not follow the pattern _id or _code but it is quite similar. Problem detection system catalog base tables only 2022-12-01 14:34 MIT License View
431 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
432 Columns that have the same name as their domain/type Find the columns that name is the same as the name of the type of the column or the domain of the column. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-30 13:04 MIT License View
433 Do not clone columns "Split a base table column into multiple columns based on the values in some other column. Each such newly created column has the name, a part of which is a data value from the original tables."(Bill Karwin) Find base tables that have more than one columns with the same type and field size and the difference between the columns are the year or month number at the end of the column name (two or four numbers, preceded by an underscore). Problem detection INFORMATION_SCHEMA only 2022-11-28 15:15 MIT License View
434 Columns that have the same name as some domain/type Use different names to avoid confusion. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-28 14:47 MIT License View
435 A setter does not update a table 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. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-27 18:35 MIT License View
436 Do not always depend on one's parent - column names are ot sufficiently different Find where a hierarchical structure is implemented in a base table by adding a foreign key that refers to a candidate key of the same table. Find only cases where the candidate key and foreign key column names are very similar (Levenshtein distance shorter than four). Problem detection system catalog base tables only 2022-11-26 17:19 MIT License View
437 Frequency of table name lengths based on the table type Find in case of base tables, materialized views, and views the number of tables based on the length of the table name. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2022-11-23 01:09 MIT License View
438 Frequency of column name lengths based on the table type Find in case of base tables, materialized views, and views the number of columns based on the length of the column name. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2022-11-22 14:43 MIT License View
439 Potential duplication of sequence generators Do not create unnecessary sequence generators. Problem detection INFORMATION_SCHEMA only 2022-11-21 11:01 MIT License View
440 The name of the routine does not match with the action of the routine Find user-defined non-trigger SQL and PL/pgSQL routines where the beginning of the name of the routine indicates a certain action inside the routine (INSERT, UPDATE, or DELETE) but there is no such statement in the routine body. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-19 14:37 MIT License View