PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1040 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 CHECK constraints that perhaps incorrectly consider 'infinity' and '-infinity' special values Such special values belong to the types DATE, TIMESTAMP, NUMERIC, REAL, and DOUBLE PRECISION. No value can be bigger than infinity or smaller than -infinity. If the check constraint cheks that a value must be bigger than -infinity or smaller than infinity, then it does not restrict (almost) anything. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
282 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 2025-11-07 10:11 MIT License View
283 CHECK constraint with pattern matching on non-textual columns This query identifies a semantic mismatch between data types and constraint logic. It targets base and foreign table columns that are defined with non-textual data types (e.g., INTEGER, DATE, BOOLEAN) but are subject to single-column CHECK constraints utilizing string pattern matching operators (LIKE, SIMILAR TO, or regular expressions). This practice forces implicit casting to text, which is computationally inefficient and indicates a design flaw. It suggests that either the column should utilize a textual data type, or the constraint should be rewritten using operators appropriate for the actual data type (e.g., numeric ranges instead of regex). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-05 19:13 MIT License View
284 CHECKs are associated with a column instead of the domain of the column Find simple check constraints (involve one column) that are associated with a base table column instead of the domain of the column. Common checks of data in columns that share the same domain should be described at the level of domain not at the level of columns. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
285 Column name contains the table name Find columns that have the same name as the table. The names may have different uppercase/lowercase characters. Make sure that the naming style is consistent. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
286 Column name is the same as the table name Find columns that have the same name as the table. The names may have different uppercase/lowercase characters. Sometimes columns with such names are used as the key columns. Make sure that the naming style is consistent. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
287 Columns defined in a subtable Find columns that have been added to a subtable, i.e., these were not defined in its immediate supertable. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
288 Columns of base tables that hold truth values but do not have a default value (non-Boolean columns) Find non-foreign key columns of base tables that probably (based on the column name) contain values that represent truth values but do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. It could be possible to select one of these as the default value in case of the columns. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
289 Columns of base tables that hold truth values but do not restrict the permitted values (non-Boolean columns) Find non-foreign key columns of base tables that probably (based on the column name) contain values that represent truth values but do not have a have a check constraint. The constraint should restrict the permitted values with values that represent truth values TRUE and FALSE. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
290 Columns of base tables that hold truth values that do not have a default value although they could have it (non-Boolean columns) Find columns of base tables that do not have type BOOLEAN but are used to record Boolean values. Based on column names these implement a state machine or record agreements. At the same time the columns do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. It should be possible to select one of these as the default value of the column. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
291 Columns that have the same name as some domain/type Use different names to avoid confusion. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
292 Columns that have the same name as their domain/type Find the columns that name is the same as the name of the type of the column or the domain of the column. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
293 Columns with BOOLEAN type that do have a good name This query identifies and lists all BOOLEAN columns that conform to the established predicate-based naming convention. It returns columns whose names begin with one of the approved semantic prefixes: is_, has_, can_, or on_. The output serves as a report of schema components that correctly adhere to best practices for clarity and self-documentation, turning column names into unambiguous true/false questions (e.g., is_agreement instead of agreed). General INFORMATION_SCHEMA+system catalog base tables 2025-11-13 13:58 MIT License View
294 Columns with BOOLEAN type that do not have a good name This query audits the naming conventions of BOOLEAN columns, enforcing a predicate-based naming convention that begins with is_, has_, can_, or on_. It specifically discourages the use of simple adjectival or past participle forms as column names. For instance, is_agreement is the preferred form over agreed, and on_kinnitatud is preferred over kinnitatud. This standard ensures the column's name is an unambiguous true/false question, which improves schema self-documentation and the readability of SQL queries. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-13 13:56 MIT License View
295 Columns with BYTEA or OID type Find columns with BYTEA or OID type. These columns are potentially meant for storing large objects. Each columns should have the most appropriate data type. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
296 Columns with JSON, JSONB, or XML type Find columns with JSON, JSONB, or XML type. Each columns should have the most appropriate data type. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
297 Columns with only one value Find base table columns that contain only one value. Perhaps it is an unnecessary column. Having only one value is most likely inadequate for testing. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 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 2025-11-07 10:11 MIT License View
299 Comments of routines Find comments of user-defined routines (functions or procedures) that are registered in the system catalog witht a COMMENT statement. Make sure that the comments give relevant, useful, and correct information. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
300 Completely overlapping foreign keys Find completely overlapping foreign keys, i.e., the same set of columns of a table is covered by more than one foreign key constraint. These constraints could refer to the same table/key or different tables/keys. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View