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
81 Checking the text maximum length with CHECK This query identifies all base table columns that enforce a maximum value length through the use of a CHECK constraint. It specifically searches for constraint definitions that contain length-calculating functions (such as length(), char_length(), or similar patterns) to provide a comprehensive list of all columns where data length is explicitly managed by a business rule at the database level. General system catalog base tables only 2025-11-13 13:05 MIT License View
82 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
83 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
84 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
85 Columns for registration and update times Find base table columns that based on the names and data types are meant for registering registration time or update time. Make sure that the columns have the same properties. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
86 Columns with array or user-defined type This query identifies columns defined with complex data types, specifically Arrays or User-Defined Types (UDTs). While PostgreSQL supports these advanced features, their indiscriminate use often signals an over-engineered schema. The query serves as a prompt to audit these columns and verify that the complex type is strictly necessary for performance or domain logic, and that a standard relational structure (e.g., scalar types or a child table) would not be a more appropriate and flexible design choice. General system catalog base tables only 2025-12-12 17:23 MIT License View
87 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
88 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
89 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
90 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
91 Comments of columns Find all comments of columns of tables. General system catalog base tables only 2025-11-07 10:11 MIT License View
92 Comments of derived tables Find comments of derived tables (views and materialized views) that are registered in the system catalog witht a COMMENT statement. Find also comments on their associated objects (columns, triggers, rules). Make sure that the comments give relevant, useful, and correct information. General system catalog base tables only 2025-11-07 10:11 MIT License View
93 Comments of non-derived tables Find comments of non-derived tables (base tables, foreign tables, and partitioned tables) that are registered in the system catalog witht a COMMENT statement. Find also comments on their associated objects (columns, constraints, indexes, triggers, rules). Make sure that the comments give relevant, useful, and correct information. General system catalog base tables only 2025-11-07 10:11 MIT License View
94 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
95 Comments of schemas, sequences, types, domains, domain constraints, and event triggers Find all the comments that have been added with a COMMENT statement to schemas, sequences, types, domains, and event triggers. General system catalog base tables only 2025-11-07 10:11 MIT License View
96 Composite foreign keys Find foreign keys that consist of more than one column. Make sure that the order of columns in the composite foreign key corresponds to the order of columns in the composite candidate key in the referenced table. General system catalog base tables only 2025-11-07 10:11 MIT License View
97 Consistency of CHECK constraint name and content Find all CHECK constraints that name contains a digit. Make sure that the name and the Boolean expression are consistent. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
98 Consistency of comments of routines Find user-defined routines that have a comment registered by the COMMENT statement and a comment within the routine body. Make sure that there are no inconsistencies between the comments. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
99 Consistency of using generic column names in candidate key columns that are not foreign key columns Find the names on base table columns that are a part of a candidate key but not a foreign key and contain a generic word (nimi, nimetus, kommentaar, kirjeldus, name, comment, description, etc.). Make sure that naming of these is consistent, i.e., such names always contain the table name or never contain the table name. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
100 Consistency of using generic column names in non-candidate key columns that are also not foreign key columns Find the names on base table columns that are not a part of a candidate key and a foreign key and contain a generic word (nimi, nimetus, kommentaar, kirjeldus, name, comment, description, etc.). Make sure that naming of these is consistent, i.e., such names always contain the table name or never contain the table name. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View