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
281 IS NULL check is probably not needed Find CHECK constraints that refer only to mandatory columns but the Boolean expression has IS NULL condition. Problem detection INFORMATION_SCHEMA only 2022-06-09 13:57 MIT License View
282 CHECK constraints with unnecessary CHECK of NULLs Find CHECK constraints that unnecessarily check that a value is missing. In a CHECK constraint, it is not necessary to check whether a value is NULL, because a CHECK constraint rejects rows where the condition evaluates to FALSE and allows rows where the condition evaluates to TRUE or UNKNOWN. If a value is missing (i.e., NULL), the result of the check is UNKNOWN. Therefore, for example, CHECK (price IS NULL OR price > 0) is equivalent to CHECK (price > 0). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-05-03 13:24 MIT License View
283 Precise comparison with pattern matching in CHECK constraints Find CHECK constraints that use precise comparison (= or <>) with a regular expression or LIKE pattern. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-01 17:13 MIT License View
284 CHECK constraints with IS NULL Find CHECK constraints to one column (associated with a base table directly or through domain) that check that the value is missing (IS NULL). Write as simple constraint definitions as possible. By default columns are optional, i.e., they permit NULLs. NULL in a column means that checking of a CHECK constraint on the column results with UNKNOWN. CHECK constraints permit rows in case of which checking results with TRUE or UNKNOWN. In case of a CHECK constraint there is no need to check separately that a value in the column could be missing, i.e., be NULL. Thus, for instance, instead of writing CHECK (price>0 OR price IS NULL) write CHECK (price>0). Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
285 Domain candidates Find column descriptions that are candidates for describing a domain. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
286 Too generic names (columns) (there is a column with a more specific name in the table) Find column names in case of which the same table has another column (with more specific name) that name contains the column name in the end or in the beginning. For instance, a base table has columns parent and root_parent and the former col-umn name is too generic, i.e., it should be more specific. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-03-20 01:01 MIT License View
287 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
288 Useless type indication Find columns and parameters where the type of the identifier is perhaps explicitly indicated in the name. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-10 14:47 MIT License View
289 Useless type indication (2) Find columns and parameters where the type of the identifier is perhaps explicitly indicated in the name. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-10 14:55 MIT License View
290 Empty columns Find columns in non-empty tables that do not contain any values. If there are no values in a columns, then it may mean that one hasn't tested constraints that have been declared to the column or implemented by using triggers. It could also mean that such columns are not needed at all. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
291 Do not register age as a number Find columns of base and foreign tables that based on the column name and type are used to register age. Problem detection INFORMATION_SCHEMA only 2024-11-06 09:55 MIT License View
292 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 2024-11-06 09:59 MIT License View
293 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 2024-11-06 10:46 MIT License View
294 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
295 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
296 Preventing strings that consist of only spaces instead of strings that consist of only whitespace characters Find columns of base tables and foreign tables where one uses a check constraint to prevent values that consist of only spaces. Make sure that this is the correct constraint and there is no need to prevent values that consist of only whitespace characters. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View
297 Incorrect prevention of the empty string or strings that consist of only spaces in a field Find columns of base tables and foreign tables where the requirement that there should not be empty strings or strings that consist of only spaces in the column has been implemented incorrectly - by using the constraint trim(column_name) IS NOT NULL. PostgreSQL (differently from Oracle) does not replace the empty string with NULL. Empty string is a value but NULL is a special marker that denotes a missing value. Thus, in case of such constraint the DBMS checks a proposition '' IS NOT NULL. This is a true proposition and the DBMS does not prevent registration of such a row. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View
298 Columns with tsvector type Find columns of base tables and materialized views that have tsvector type. General INFORMATION_SCHEMA+system catalog base tables 2023-12-22 12:41 MIT License View
299 Only one value permitted in a non-inherited base table or a foreign table column (based on check constraints) Find columns of base tables or foreign tables in case of which a check constraint on the column permits only one value in the column. Exclude columns that are inherited from a supertable because the constraint may be correct if it is applied to a column of a subtable that is inherited from the supertable. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-04-30 20:16 MIT License View
300 Only one value permitted in a base table or a foreign table column (based on check constraints) Find columns of base tables or foreign tables in case of which a check constraint on the column permits only one value in the column. The constraint may be correct if it is applied to a column of a subtable that is inherited from the supertable or is used to enfore the rule that the table can have at most one row. General INFORMATION_SCHEMA+system catalog base tables 2024-04-30 20:14 MIT License View