Filter 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 the relatively small amount of data in the system catalog of a database.
#1. All clustered indexes
system catalog base tables onlyFind all tables that rows have been physically sorted based on an index.
#2. All database privileges
system catalog base tables onlyFind the privileges to use the database. c - CONNECT; C - CREATE; T - TEMPORARY.
#3. All event triggers
system catalog base tables onlyFind event triggers, which are not associated to a specific schema object.
#4. All exclude constraints
system catalog base tables onlyFind all exclude constraints.
#5. All foreign key constraints
system catalog base tables onlyEnforce referential integrity in database. Find all referential integrity (foreign key) constraints.
#6. All identifying relationships
system catalog base tables onlyFind all non-identifying relationships where the foreign key (a set of columns) is a subset of a uniqueness constraint (primary key, unique, or exclude constraint).
#7. All key constraints
system catalog base tables onlyThis query retrieves a comprehensive list of all PRIMARY KEY and UNIQUE constraints defined on base tables within the database. These constraints are the fundamental mechanisms for enforcing entity integrity and uniqueness. The output provides essential information for schema auditing, documentation generation, and analyzing the data model's key structures. It allows administrators and developers to quickly verify how uniqueness is enforced for each table.
#8. All non-identifying relationships
system catalog base tables onlyFind all non-identifying relationships where the foreign key (a set of columns) is not a subset of any uniqueness constraint (primary key, unique, or exclude constraint).
#9. All rules
system catalog base tables onlyFind user-defined rules for rewriting data manipulation language statements. Rules should be used only for the tasks that cannot be achieved in a declarative manner, i.e., for example, by declaring a constraint.
#10. All security policies
system catalog base tables onlyFind all security policies.
#11. All supertables
system catalog base tables onlyFind all the base tables that serve as supertables in the inheritance hierarchies
#12. All system-defined TOAST-able types
system catalog base tables onlyFind system-defined types in case of which the system can use the TOAST technique, i.e., save the value in a compressed form or store it in a automatically-created secondary table, which is hidden from the database user (TOAST table).
#13. All uniqueness constraints where nulls are not distinct
system catalog base tables onlyThis query identifies UNIQUE constraints that explicitly use the NULLS NOT DISTINCT clause. Introduced in PostgreSQL 15, this modifier changes standard SQL behavior by treating NULL values as equal, thereby preventing multiple identical rows that contain NULLs. This query is useful for auditing the schema to review where this specific null-handling logic is applied, ensuring it aligns with business rules, or flagging it for environments that require compatibility with older PostgreSQL versions
#14. All user-defined TOAST-able types
system catalog base tables onlyFind user-defined types in case of which the system can use the TOAST technique, i.e., save the value in a compressed form or store it in a automatically-created secondary table, which is hidden from the database user (TOAST table).
#15. All user mappings
system catalog base tables onlyFind all user mappings for foreign servers and tables
#16. A state machine is implemented with the help of a state classifier table
system catalog base tables onlyFind implementations of state machines that use a state classifier table.
#17. Base table columns where TOASTing is possible
system catalog base tables onlyFind all base table columns in case of which the system can use TOAST technique.
#18. Base table columns where TOAST-ing strategy has been changed
system catalog base tables onlyFind base table columns in case of which the system can use TOAST technique (due to the data type of the column) and where the toasting strategy has been changed so that it is different than the default strategy determined by the type. Make sure that the new strategy is optimal.
#19. Base table columns where TOAST-ing strategy has been changed to plain
system catalog base tables onlyFind base table columns in case of which the system can use TOAST technique (due to the data type of the column) and where the toasting strategy has been changed to plain. It means that potentially, if a value in the column is large enough, it is not possible to save the row.
#20. Base table columns with a composite type
system catalog base tables onlyFind base table columns with a composite type. Think through as to whether a column with a composite type could be replaced with a separate table.
| # | Name(activate to sort ascending) | Goal(activate to sort ascending) | Type(activate to sort ascending) | Data source(sorted descending, activate to sort ascending) | Last update(activate to sort ascending) | License | Actions |
|---|---|---|---|---|---|---|---|
| 1 | All clustered indexes | Find all tables that rows have been physically sorted based on an index. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 2 | All database privileges | Find the privileges to use the database. c - CONNECT; C - CREATE; T - TEMPORARY. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 3 | All event triggers | Find event triggers, which are not associated to a specific schema object. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 4 | All exclude constraints | Find all exclude constraints. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 5 | All foreign key constraints | Enforce referential integrity in database. Find all referential integrity (foreign key) constraints. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 6 | All identifying relationships | Find all non-identifying relationships where the foreign key (a set of columns) is a subset of a uniqueness constraint (primary key, unique, or exclude constraint). | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 7 | All key constraints | This query retrieves a comprehensive list of all PRIMARY KEY and UNIQUE constraints defined on base tables within the database. These constraints are the fundamental mechanisms for enforcing entity integrity and uniqueness. The output provides essential information for schema auditing, documentation generation, and analyzing the data model's key structures. It allows administrators and developers to quickly verify how uniqueness is enforced for each table. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 8 | All non-identifying relationships | Find all non-identifying relationships where the foreign key (a set of columns) is not a subset of any uniqueness constraint (primary key, unique, or exclude constraint). | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 9 | All rules | Find user-defined rules for rewriting data manipulation language statements. Rules should be used only for the tasks that cannot be achieved in a declarative manner, i.e., for example, by declaring a constraint. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 10 | All security policies | Find all security policies. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 11 | All supertables | Find all the base tables that serve as supertables in the inheritance hierarchies | Sofware measure | system catalog base tables only | MIT (opens in new tab) | View | |
| 12 | All system-defined TOAST-able types | Find system-defined types in case of which the system can use the TOAST technique, i.e., save the value in a compressed form or store it in a automatically-created secondary table, which is hidden from the database user (TOAST table). | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 13 | All uniqueness constraints where nulls are not distinct | This query identifies UNIQUE constraints that explicitly use the NULLS NOT DISTINCT clause. Introduced in PostgreSQL 15, this modifier changes standard SQL behavior by treating NULL values as equal, thereby preventing multiple identical rows that contain NULLs. This query is useful for auditing the schema to review where this specific null-handling logic is applied, ensuring it aligns with business rules, or flagging it for environments that require compatibility with older PostgreSQL versions | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 14 | All user-defined TOAST-able types | Find user-defined types in case of which the system can use the TOAST technique, i.e., save the value in a compressed form or store it in a automatically-created secondary table, which is hidden from the database user (TOAST table). | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 15 | All user mappings | Find all user mappings for foreign servers and tables | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 16 | A state machine is implemented with the help of a state classifier table | Find implementations of state machines that use a state classifier table. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 17 | Base table columns where TOASTing is possible | Find all base table columns in case of which the system can use TOAST technique. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 18 | Base table columns where TOAST-ing strategy has been changed | Find base table columns in case of which the system can use TOAST technique (due to the data type of the column) and where the toasting strategy has been changed so that it is different than the default strategy determined by the type. Make sure that the new strategy is optimal. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 19 | Base table columns where TOAST-ing strategy has been changed to plain | Find base table columns in case of which the system can use TOAST technique (due to the data type of the column) and where the toasting strategy has been changed to plain. It means that potentially, if a value in the column is large enough, it is not possible to save the row. | Problem detection | system catalog base tables only | MIT (opens in new tab) | View | |
| 20 | Base table columns with a composite type | Find base table columns with a composite type. Think through as to whether a column with a composite type could be replaced with a separate table. | General | system catalog base tables only | MIT (opens in new tab) | View |