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
521 Non-key and non-foreign key base table columns with the same name and type that have in some cases permit NULLs and in some cases not Find non-key and non-foreign key base table columns with the same name and type that in some cases permit NULLs and in some cases not. Be consistent. Make sure that this selection is consistent. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
522 Non-predefined character classes must not be between double square brackets Write correct regular expressions. For instance, if there is a rule that code must consist of one or more digits, then correct expression is code~'^[0-9]+$', not code~'^[[0-9]]+$'. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
523 Non-updatable views that have data modification privileges Be precise and do not give impossible privileges. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
524 No-operation routines with static return values This query identifies SQL routines that are functionally equivalent to a no-operation (no-op) instruction, meaning their sole operation is to return either a constant literal or an unmodified input parameter. Such routines provide no transformation or logic. They are typically superfluous and may represent placeholder code from early development, refactoring artifacts where original logic was deprecated, or simple logical oversights. Eliminating these functions reduces code clutter, simplifies application logic, and removes a marginal but unnecessary layer of computational overhead. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-15 11:06 MIT License View
525 No point to have in a procedure COMMIT without ROLLBACK or vice versa If you end transaction in a procedure, then there should be a possibility to either commit or rollback the transaction based on some condition. Procedures appeared in PostgreSQL 11. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
526 Not equals check in unstandardized way Find user-defined routines that use != operator to test as to whether two values are not equal. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
527 NOT IN or <> ALL in derived tables Avoid using NOT IN or <>ALL with a non-correlated subquery in PostgreSQL because the query performance will be very poor, especially in case of large data sizes. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
528 NOT IN or <> ALL in routines Avoid using NOT IN or <>ALL with a non-correlated subquery in PostgreSQL because the query performance will be very poor. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
529 NOT NULL constraint is directly associated with a column instead of the domain of the column Find mandatory (NOT NULL) base table columns that have been defined based on the same domain but the NOT NULL constraint is associated directly with the column not to the domain. PostgreSQL CREATE DOMAIN statement documentation points out that it is possible to add NULL's to columns that have a NOT NULL domain and thus suggests to associate NOT NULL constraints with a column instead of the domain. However, this is a non-standard behavior and defeats the idea of domain as a reusable asset. The scenarios where NULLs can appear in columns with a NOT NULL domain are quite exotic and probably cannot appear in production environments. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
530 NOT NULL domains Find domains with NOT NULL constraints and base table columns that have been defined based on the domain. PostgreSQL CREATE DOMAIN statement documentation points out that it is possible to add NULL's to columns that have a NOT NULL domain and thus suggests to associate NOT NULL constraints with a column instead of the domain. However, this is a non-standard behavior and defeats the idea of domain as a reusable asset. The scenarios where NULLs can appear in columns with a NOT NULL domain are quite exotic and probably cannot appear in production environments. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
531 Number of columns covered with constraints For different types of constraints find the number of columns covered with constraints of such type. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
532 Number of derived tables that aggregate data Find the number of derived tables that aggregate data. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
533 Number of tables covered by derived tables Find the number of base tables, the number of base tables that are referred from at least one derived table (view or materialized view), the number of base tables that are referred from at least one view, and the number of base tables that are referred from at least one materialized view. If the database is used through the public database interface (virtual data layer), then, ideally, each table is referred from the subquery of at least one derived table. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
534 Number of underlying tables of derived tables Find for each view or materialized view the number of tables based on which the derived table has been directly defined. These tables could be base tables or derived tables. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
535 Number of used tables Find statistics about how many derived tables have how many different underlying tables. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
536 Number of using views Find statistics about how many base tables have how many derived tables that use these tables. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
537 Number of views with and without security barrier Find the number of views, the number of views with and without security barrier setting, and the names of views with and without the security barrier setting. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
538 ON DELETE CASCADE is not needed (based on classifier tables) This query identifies foreign key constraints with ON DELETE CASCADE that reference classifier tables. Classifier tables hold reference data (e.g., status types, categories), and their rows should not be deleted if they are in use. Applying ON DELETE CASCADE to such a relationship is a critical design flaw, as it creates a direct path for the deletion of a single lookup value to trigger the mass deletion of operational business data. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-08 10:49 MIT License View
539 Only ID primary key Find base base tables have the simple primary key that contains a column with the (case insensitive) name id and an integer type. In addition, the primary key values are generated automatically by the system by using a sequence generator. In addition the base table must not have any unique constraint. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
540 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 2025-11-07 10:11 MIT License View