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
361 Frequent names of database objects by object type "Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) Names should be expressive. Find the names (identifiers) of user-defined database objects that occur at least twice as frequently as a name occurs in average in case of the particular type of database objects. Also make sure that there is no duplication in play. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2024-12-21 17:24 MIT License View
362 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 2023-11-07 09:22 MIT License View
363 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 2023-11-07 09:22 MIT License View
364 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 2021-02-25 17:29 MIT License View
365 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 2021-11-04 17:26 MIT License View
366 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 2021-11-04 10:50 MIT License View
367 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 2021-02-25 17:30 MIT License View
368 Generated stored base table columns Find generated stored columns in PostgreSQL base tables. The support of generated columns was added to PostgreSQL 12. These could be used to improve the performance of queries. General INFORMATION_SCHEMA only 2020-11-06 14:51 MIT License View
369 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 2021-02-25 17:30 MIT License View
370 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 2021-02-25 17:29 MIT License View
371 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 2024-01-07 13:43 MIT License View
372 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 2024-01-07 13:43 MIT License View
373 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 2024-01-07 13:42 MIT License View
374 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 2024-01-07 13:43 MIT License View
375 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 2024-01-07 13:43 MIT License View
376 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 2024-01-07 13:43 MIT License View
377 Granted roles Find membership relations between roles. General system catalog base tables only 2024-01-07 13:30 MIT License View
378 Grantees Database must be used by users who have minimal set of privileges for performing tasks. The query helps to find out as to whether some user/role other than PUBLIC and a superuser have rights to use tables and routines of the database. General INFORMATION_SCHEMA only 2020-11-06 14:51 MIT License View
379 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 2021-02-25 17:29 MIT License View
380 Gratuitous context in the names of non-foreign key and non-candidate key columns Find the names on base table columns that are not a part of a candidate key and a foreign key and that contain the name of the table. Exclude very general column names (for instance, nimi, nimetus, kommentaar, kirjeldus, name, comment, description). In case of these using the table name in the column name is not a problem because it simplifies writing the queries based on the tables. In this case one does not have to rename the columns in the query result. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-15 13:39 MIT License View