PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1031 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
441 Foreign servers without user mappings Find foreign servers that do not have any associated user mappings. "A user mapping typically encapsulates connection information that a foreign-data wrapper uses together with the information encapsulated by a foreign server to access an external data resource." Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
442 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 2025-11-07 10:11 MIT License View
443 FOR UPDATE is not allowed with aggregate functions Implement explicit locking correctly. Instead of writing, for instance, SELECT Count(*) AS cnt FROM person WHERE person_id=1 FOR UPDATE; one has to write SELECT person_id FROM person WHERE person_id=1 FOR UPDATE; Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
444 FOR UPDATE is not needed if there is no FROM clause in the SELECT statement Find routines that use SELECT … FOR UPDATE without selecting rows from a specific table. For instance: SELECT 'text' AS v FOR UPDATE; Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
445 Full text search columns that have no gin or gist index Find columns of base tabels and materialized views with the type tsvector that do not have a gin or a gist index. These are the preferred index types for text search. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
446 Full text search columns with other type of index than gin or gist index Find columns of base tabels and materialized views with the type tsvector that do not have a gin or a gist index but have another type of index (e.g., b-tree). Gin and Gist are the preferred index types for text search. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
447 Function in a function-based index of a column is different from the function that is used in the subquery of a derived table Find cases where the function of a function-based index of a column is different from the function that is used in the query in a derived table based on the column. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
448 Function in a function-based index of a column is different of the function that is used in the query in a routine based on the column Create appropriate indexes to speed up queries. If you apply a function to a column in a query, then create a function-based index based on the function to the column. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
449 Functions that have transactional control Find functions that contain transactional control statements (BEGIN, START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT) in their body. PostgreSQL does not permit transaction control in functions. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
450 Function Upper or Lower is used in an index on a non-textual column Find function-based indexes that are based on function Upper or Lower but have been defined on a non-textual column. Such indexes support case insensitive search but in case of non-textual columns this does not have a meaning. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
451 Generated stored base table columns duplicates another column in the table Find generated stored columns in PostgreSQL base tables that duplicate other columns in the table. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
452 Generated stored base table columns that expression does not refer to any column Find generated stored base table columns that expression does not refer to any column of the table. It could be that there will be a constant value in every row in case of this column. The support of generated columns was added to PostgreSQL 12. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
453 Grantable column privileges Find column privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
454 Grantable privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers Find privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
455 Grantable roles Find roles that a member can grant to others, i.e., the role has been granted with ADMIN OPTION. The number of privileges that can be passed on should be as small as possible. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
456 Grantable routine privileges Find routine privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
457 Grantable table privileges Find table privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
458 Grantable usage privileges Find usage privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
459 Gratuitous context in the names of foreign key columns Find foreign key columns that name contains twice the name of the referenced (primary) table. Problem detection system catalog base tables only 2025-11-07 10:12 MIT License View
460 Gratuitous context in the names of non-foreign key and non-primary key columns This query identifies base table columns that unnecessarily include the table name. It searches for columns that are not part of a primary or foreign key and contain the name of their parent table. To avoid flagging legitimate naming conventions, it explicitly excludes a list of generic column names (e.g., name, description, nimi, kommentaar) where prefixing with the table name is considered good practice for improving clarity in queries. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-05 10:01 MIT License View