PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1036 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
201 Constraints that are redefined in a subtable. Find primary key, unique, foreign key, and exclude constraints that have been defined in a supertable (parent table) and have been redefined in its subtable. General system catalog base tables only 2025-11-07 10:11 MIT License View
202 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 2025-11-07 10:11 MIT License View
203 Constraints with the same name within the same schema and constraint type Find names of foreign key constraints that are used within the same schema more than once. Find names of check constraints that are used within the same schema more than once. Find names of constraint triggers that are used within the same schema more than once. Different things should have different names. But here different constraints have the same name. Also make sure that this is not a sign of duplication. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
204 Coverage by derived tables Find for each base table the list of derived tables (both views and materialized views) that refer to the base table. 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. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
205 Coverage by routines that have the SQL-standard body Find for each base table the list of routines (functions and procedures) that refer to the base table. If the database is used through the public database interface (virtual data layer), then, ideally, each table is referred from at least one routine. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
206 Cycle in a hierarchy There should not be cycles in hierarchies meaning that the parent must always be specified. In this case a parent must reference to a child or to itself, otherwise it cannot be registered. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
207 Cycles in relationships Find as to whether foreign key constraints between tables form a cycle. For instance, if table C refers to table B, table B refers to table A, table A refers to table C and all the foreign key columns are mandatory, then one cannot add data to the tables. Is cycle value in the result should not contain value TRUE. One should check manually as to whether the foreign key columns are mandatory or optional. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
208 Database can be accessed through PUBLIC privileges Find as to whether the database access by users (applications) can take place thanks to PUBLIC privileges, i.e., find as to whether PUBLIC (all current and future users) has CONNECT privilege in the database. PUBLIC gets the privilege by default. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
209 Database connect privilege is missing Find non-superusers who have a privilege to use a table or a routine but do not have the privilege to connect to the database. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
210 Database objects of the same type and case insensitive name in the same container Find database objects with the same type and case insensitive name in the same container. It can only happen if some of the names are case insensitive and others are case sensitive. For instance, the same schema can contain the table "Client" and client Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
211 Database object that belong to the public interface (virtual data layer) and that names contain the letters õäöüÕÄÖÜ Find database object that belong to the database public interface (virtual data layer - consists of routines and derived tables) and that names contain the letters õäöüÕÄÖÜ (Estonian letters with a diacritic). These letters belong to the Estonian alphabet but do not belong to the ASCII character set. Although permitted by the DBMS, such letters might make it more difficult to use the interface by other programs. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
212 Database object that do not belong to the public interface (virtual data layer) and that names contain the letters õäöüÕÄÖÜ Find database object that do not belong to the database public interface (virtual data layer - consists of routines and derived tables) and that names contain the letters õäöüÕÄÖÜ (Estonian letters with a diacritic). These letters belong to the Estonian alphabet but do not belong to the ASCII character set. Although permitted by the DBMS, such letters might make the naming style inconsistent with the naming style of elements that belong to the public interface. If applications access base tables directly, then the letters can cause the same problems as in case of derived tables, i.e., applications may have difficulties with such names. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
213 Data type mismatch in check constraints This query identifies single-column CHECK constraints where the validation logic utilizes operators or functions that are incompatible with the column's native data type. It detects cases where the database must perform implicit casting to evaluate the expression (e.g., performing arithmetic on a TEXT column or string manipulation on a DATE column). Relying on implicit coercion in constraints involves unnecessary computational overhead and frequently indicates a fundamental error in data modeling or constraint formulation. Problem detection system catalog base tables only 2025-12-05 19:23 MIT License View
214 Data type usage in the base table columns Get overview of used data types in the columns of base tables. If the selection is very small then this is a warning sign that perhaps unsuitable types have been used. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
215 Declaratively partitioned tables with one partition Find declaratively partitioned tables that have exactly one partition. Declarative partitioning is implemented in PostgreSQL starting from PostgreSQL 10. If there is only one partition, then it raises question as to why the additional complexity associated with partitioning is needed. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
216 Declaratively partitioned tables without partitions Find declaratively partitioned tables that do not have any partitions. Declarative partitioning is implemented in PostgreSQL starting from PostgreSQL 10. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
217 Default is NULL Find all tables where default value is NULL. NULL is the marker that denotes missing value. Implicitly all columns that do not have a default value have the default NULL. There is no need to specify such default value. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
218 Default should be declared at the level of domain not at the level of base table columns Find domains that have been used to define one or more base table non-foreign key columns and all the columns have the same default value that is associated directly with the column not with the domain. Write as little code as possible. If possible, move things "before the brackets" so to say. In this case it means declaring the default value at the level of the domain and not at the level of base table columns. An exception is when the domain is used to define foreign key columns. In this case, it would be appropriate to define the default value at the column level (because different foreign keys could have different default values). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
219 Deferrable constraints Find all deferrable constraints. General system catalog base tables only 2025-11-07 10:11 MIT License View
220 Deferrable foreign key constraint with a RESTRICT compensating action Find deferrable foreign key constraint with ON UPDATE RESTRICT or ON DELETE RESTRICT compensating action. Referential actions are carried out before, and are not part of, the checking of a referential constraint. Deferring a referential constraint defers the checking of the of the constraint (a ) but does not defer the referential actions of the referential constraint. In PostgreSQL the essential difference between NO ACTION and RESTRICT is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not. Thus RESTRICT could result with the failure of data modification where in case of NO ACTION the modification would succeed. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View