PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1041 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
841 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
842 Sorting rows based on random values in derived tables Find derived tables (views and materialized views) that sort rows based on random values. This can be used to find a random subset of rows. It is a computationally expensive operation. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
843 Sorting rows based on random values in derived tables without limiting rows Find derived tables (views and materialized views) that sort rows based on random values but do not limit the number of rows. This is unnecessary because without sorting the rows are returned in a unspecified order. Sorting based on random values is a computationally expensive operation. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
844 Sorting rows based on random values in routines Find routines that contain a statement that sorts rows based on random values. This can be used to find a random subset of rows. It is a computationally expensive operation. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
845 Sorting rows based on random values in routines without limiting rows Find routines that contain a statement that sorts rows based on random values but do not limit the number of rows. This is unnecessary because without sorting the rows are returned in a unspecified order. Sorting based on random values is a computationally expensive operation. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
846 SQL function does not return a value Find SQL functions that do not return a value (return VOID) but the SQL statement in the function has RETURNING clause. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
847 STATEMENT level triggers and ROW level AFTER triggers without RETURN NULL Write correct code "The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null." (PostgreSQL documentation) Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
848 STATEMENT level triggers that refer to the values of row variables NEW or OLD Find STATEMENT level triggers that refer to the values of row variables NEW or OLD. NEW and OLD are special variables that can only be used in row-level trigger procedures. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
849 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-11-07 10:11 MIT License View
850 Stating the obvious (2) Find the names of database objects where the name of the database object contains a part of the name of the object type. For instance, the query finds base tables, were the name contains fragments _base, base_, _table, or table_. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
851 Stating the obvious (column names) Find the names of columns where the name of the column contains a part of the name of the data type of the column. For instance, the query finds columns, were the name contains fragments integer_ or _integer. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
852 Storing a duration as time Find columns of base and foreign tables that based on the column names are used to register durations but the type of the column is time. "It is possible to use a TIME data type if the duration is less than 24 hours, but this is not what the type is intended for, and can be the cause of confusion for the next person who has to maintain your code." Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
853 Storing a duration rather than a point in time Find columns of base and foreign tables that based on the column names and types are used to register start time and duration rather than start time and end time. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
854 Storing file content in the database Find columns that probably store content of files in the database. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
855 Subqueries of derived tables with LIMIT/FETCH/DISTINCT ON without ORDER BY Find subqueries of derived tables (views, materialized views) with the LIMIT/FETCH clause or with DISTINCT ON construct but without the ORDER BY clause. These constructs require sorting to produce a meaningful result. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
856 Surrogate key columns Find surrogate keys. Surrogate key is a key that consist of one column, which has an integer type. The key has been declared by using PRIMARY KEY or UNIQUE constraint. The column is associated with a sequence generator (either external or internal, i.e., created by the system automatically because the column has been declared as an identity column). The column does not participate in any foreign key. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
857 Surrogate key columns that do not follow the naming style Find surrogate key columns that name does not end with "id_" or start with "id_". Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
858 System-generated domain CHECK constraint names Find the names of domain CHECK constraints that have been system-generated. Names should follow the same style. If there is a mix of system-generated and user-defined names, then the style is most probably different. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
859 System-generated table constraint names (constraints that involve one column) Find the names of database constraints that have been system-generated. Additional restrictions are that the constraints must involve only one column and are associated directly with a table (not through a domain). Names should follow the same style. If there is a mix of system-generated and user-defined names, then the style is most probably different. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
860 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 2025-11-07 10:11 MIT License View