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
481 Constraint-supporting UNIQUE indexes with the same leading column Find indexes that support a uniqueness constraint and have the same leading column. General system catalog base tables only 2023-11-26 16:01 MIT License View
482 All partial indexes Find indexes to a subset of table rows. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
483 All gin indexes Find indexes with less common access methods. Gin indexes are, for instance, used to speed up PostgreSQL's built in full text search. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
484 Tables that have associated user triggers Find information about tables that are associated with triggers. General system catalog base tables only 2020-11-06 14:51 MIT License View
485 Table inheritance Find inheritance between base tables. Use table inheritance carefully because, for instance, certain constraints are not inherited and must be redefined on child tables. General system catalog base tables only 2020-11-15 13:02 MIT License View
486 Multiple inheritance Find instances of multiple inheriance of base tables. Make sure that multiple inheritance is indeed needed. General system catalog base tables only 2020-11-15 12:20 MIT License View
487 Mandatory non-primary key columns Find mandatory non-primary key columns, i.e., the columns that have NOT NULL constraint. General INFORMATION_SCHEMA+system catalog base tables 2020-12-05 19:35 MIT License View
488 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 2023-10-20 15:34 MIT License View
489 Mandatory columns for holding large textual values (comments, descriptions, etc.) Find mandatory (NOT NULL) base table columns that name, column type, and field size refers to the possibility that these are used to register large textual values like comments, descriptions, and explanations. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
490 Duplicate materialized views Find materialized views with exactly the same subquery. There should not be multiple materialized views with the same subquery. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication. Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View
491 Granted roles Find membership relations between roles. General system catalog base tables only 2024-01-07 13:30 MIT License View
492 CHECK constraints with the cardinality bigger than one Find multicolumn CHECK constraints. Such constraints must be associated directly with a base table, i.e., these cannot be associated with a domain. Enforce as much data integrity as possible at the database level and prefer declarative constraints to a trigger. General system catalog base tables only 2023-12-25 12:38 MIT License View
493 Duplicate rules Find multiple rules with the same definition (event, condition, action) on the same table. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication. Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View
494 Input parameters with the same name have different types Find named input parameters of routines that have the same name but different type. It could be a mistake or deliberate due to overloading of routines. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-01-24 15:01 MIT License View
495 Unused named input parameters Find named input parameters that are not referenced in the routine body. All the parameters that are presented in the routine signature declaration must be used in its body. Otherwise these are dead code elements. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-05 13:03 MIT License View
496 Perhaps a too long name, which has been automatically shortened Find names (identifiers) of user-defined database objects that are 63 bytes long. This is the longest permitted length of identifiers if the default value of the NAMEDATALEN parameter has not been changed. PostgreSQL shortens too long identifiers automatically. Automatic code modification could break it somewhere. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-21 16:46 MIT License View
497 Names of database objects (regular identifiers) that contain $ Find names (identifiers) of user-defined database objects that are regular identifiers and contain the $ sign starting from the second position. "Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable." (PostgreSQL manual) Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-21 16:46 MIT License View
498 Perhaps check constraint names contain incorrect or unnecessary words Find names of check constraints (either associated with a base table or a domain) that names contain words that are not needed in the name. For instance, constraints cannot ensure the correctness of data and thus the word correct should not be used in the names. Words like "valid" or phrases like "follows_rules" are just noise because all the constraint ensure that the registered data values are valid and follow certain rules. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-10-06 14:10 MIT License View
499 Names of the password columns Find names of columns of base tables, views, and materialized views that contain passwords. Make sure that the naming is consistent, General INFORMATION_SCHEMA+system catalog base tables 2022-11-30 15:04 MIT License View
500 Names of database objects that contain dollar sign Find names of database objects that contain a dollar sign ($) that is not the first symbol of the name. In PostgreSQL regular identifiers cannot start with $. However, $ can be used in other positions of the name. "Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable." (PostgreSQL documentation) Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-21 16:43 MIT License View