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
801 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
802 Snake_case violations detected by common suffixes This query identifies database identifiers (columns, parameters, etc.) that likely violate the snake_case naming convention based on suffix analysis. It flags names ending with common temporal or attributional terms (date, time, by) where the suffix is not immediately preceded by an underscore. This pattern is highly indicative of camelCase (e.g., createdDate, updatedBy) or PascalCase usage. Adhering to snake_case (e.g., created_date, updated_by) is the recommended standard for SQL database schemas to ensure consistency and readability. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-27 11:13 MIT License View
803 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
804 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
805 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
806 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
807 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
808 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
809 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
810 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
811 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
812 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
813 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
814 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
815 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
816 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
817 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
818 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
819 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
820 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