PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1040 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
821 SELECT * in a routine body SELECT statement should list the columns not use SELECT * to return data from all the columns. Firstly, it ensures, that the query asks only data that is really needed by the routine. It means less data that the DBMS has to fetch and pass to the routine. It could also mean that the DBMS can answer to a query based on an index without reading table blocks. Secondly, it documents the data that is returned by the query. The query does not consider objects that are a part of an extension. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
822 Sequence generators not needed Find possible classifier tables that have a column with a sequence generator. Such tables should have natural keys instead of surrogate keys. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
823 Sequences that are not owned by a table column Find sequence generators that are not owned by a table column, i.e., if one drops the table or the column, then the sequence generator stays in place. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
824 SET DEFAULT compensatory action is unsuitable Find foreign keys with SET DEFAULT compensatory action where the foreign key column does not have a default value. Compensatory actions cannot make changes that violate integrity constraints in a database. SET DEFAULT means that there shoud be a default value at the foreign key column. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
825 SET NULL compensatory action is unsuitable Find foreign key constraints that use a SET NULL compensating action but a foreign key column is mandatory, i.e., does not permit NULLs. Compensatory actions cannot make changes that violate integrity constraints in a database. SET NULL cannot put NULL to a mandatory column (delete a foreign key value). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
826 Set operations that do not remove duplicate rows in derived tables Find derived tables (views and materialized views) that use a set theoretic operation (union, except or intersect) in a manner that does not remove duplicate rows and thus can produce a multiset not a set. Make sure that it is what is needed. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
827 Short cycle (columns) Find cases where two candidate keys of the same table that are also foreign keys reference to each other. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
828 Short cycles (tables) Find pairs of tables that have both a mandatory (NOT NULL) and not defrerrable foreign key that references to the other table. Such cycles can involve more than two tables but the query detects only cycles with two tables. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
829 Should the time zone be recorded in case of time or not? Find all the base table columns that have the type time without time zone or time with time zone. Return the data only if there is at least one column with the type time without time zone and one column with the type time with time zone. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
830 Should the time zone be recorded in case of timestamp or not? Find all the base table columns that have the type timestamp without time zone or timestamp with time zone. Return the data only if there is at least one column with the type timestamp without time zone and one column with the type timestamp with time zone. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
831 Simple check constraints with multiple tasks Find simple check constraints, i.e., check constraints that cover one column that seem to have multiple tasks. The corresponding code smell in case of cleaning code is "G30: Functions Should Do One Thing". (Robert C. Martin, Clean Code) Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
832 Simple primary keys that column name does not contain the table name Find simple primary keys that column name does not contain the table name. The naming should be clear and consistent. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
833 Small tables Find tables that have one column or zero columns. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
834 Some candidate key values cannot be used as foreign key values Find foreign key constraints in case of which some candidate key values cannot be used as foreign key values. Primary key/unique columns and foreign key columns should have the same data type and field size. If, for instance, the primary key column has type INTEGER and foreign key column has type SMALLINT, then one cannot use all the primary key values as foreign key values. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
835 Some CHECKS are associated with a domain and some with the base table columns that have the domain Find cases where some CHECKS are associated with a domain and some with the base table columns that have the domain. Avoid duplication of code. Write as little code as possible. If possible, move things "before the brackets" so to say. In this case it means declaring CHECKS at the level of the domain and not at the level of base table columns. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
836 Some data modification functions return a value and some not Find as to whether there are data modification routines that return a value as well as data modification routines that do not return a value. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
837 Something is still to do in routines Find routines where comments contain TODO phrase. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
838 Sometimes current_timestamp, sometimes now() Find as to whether you sometimes use current_timestamp function and sometimes now() function. These implement the same functionality. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
839 Sometimes extract, sometimes date_part Find as to whether you sometimes use date_part function and sometimes extract function. These implement the same functionality. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
840 Sometimes regexp_like, sometimes ~ Find as to whether you sometimes use regexp_like function and sometimes ~ operator. These implement the same functionality. regexp_like function that was added to PostgreSQL 15 and provides the same functionality as ~ and ~* operators. Try to be consistent. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View