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
601 Base tables where all the unique columns are optional Find the base tables where all the unique columns are optional. In such tables there can be rows without values that identify these rows. In this case there can be rows in the table where the values that should identify the row are missing. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
602 Different foreign key column names in case of referencing the same candidate key Find the cases when the names of columns in different foreign keys that reference to the same candidate key are different. If different names reflect different roles, then it is legitimate. However, there could also be accidental differences that makes it more difficult to use the database. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
603 Columns that have the same name as their domain/type Find the columns that name is the same as the name of the type of the column or the domain of the column. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
604 Using system-defined names of constraints (constraints that involve more than one column) Find the constraint types in case of which there exists system-defined names. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
605 Using system-defined names of constraints (constraints that involve one column) Find the constraint types in case of which there exists system-defined names. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
606 Derived tables that have a column with the xid type Find the derived tables (views and materialized views) that have a column with the xid type, i.e., these use the data from the hidden xmin column of a base table. If one uses optimistic approach for dealing with the concurrent data modifications, then xmin values should be presented by views and used in routines that modify or delete rows. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
607 Different non-surrogate key default values Find the different default values that implement something other than a surrogate key. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
608 Different search paths of SECURITY DEFINER functions Find the different search paths used in case of SECURITY DEFINER functions and the number of their occurrences. Make sure that these have been specified correctly and consistently and that they do not refer to any non-existent schemas. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
609 The number and percentage of base tables without CHECK constraints Find the extent in which data integrity is checked at the database level. Find the number and percentage of base tables that do not have any associated CHECK constraints. Sofware measure INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
610 The number and percentage of base tables without keys Find the extent in which repeating rows are permitted in the database. Find the number and percentage (from the total number of base tables) of base tables that do not have the PRIMARY KEY constraint and also do not have any UNIQUE constraints. Sofware measure INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
611 The longest referential paths Find the longest referential paths between the tables regardless of the schema that contain the tables. Sofware measure system catalog base tables only 2025-11-07 10:11 MIT License View
612 Names of database objects that contain a digit Find the names (identifiers) of user-defined database objects that contain at least one digit. Names should be informative. Duplicates should be avoided. Digits in names are a possible sign of duplication of database objects or unclear names. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
613 Too generic names (columns) Find the names of columns that are too generic. In SQL databases data/information is represented as values in columns. It is not a good style to use generic words like data, information, column, etc. In the names of columns. Moreover, avoid too generic column names like: id, tyyp, kood, aeg, kp,type, code, time, date, fk, pk. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
614 Stating the obvious (column names) Find the names of columns where the name of the column contains a part of the name of the data type of the column. For instance, the query finds columns, were the name contains fragments integer_ or _integer. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
615 System-generated table constraint names (constraints that involve one column) Find the names of database constraints that have been system-generated. Additional restrictions are that the constraints must involve only one column and are associated directly with a table (not through a domain). Names should follow the same style. If there is a mix of system-generated and user-defined names, then the style is most probably different. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
616 Perhaps 0 instead of o Find the names of database objects where 0 sign is perhaps used instead of o. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
617 Names of database objects with perhaps too many digits Find the names of database objects where more than half the signs are digits. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
618 Stating the obvious (2) Find the names of database objects where the name of the database object contains a part of the name of the object type. For instance, the query finds base tables, were the name contains fragments _base, base_, _table, or table_. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
619 Naming of declarative base table constraints Find the names of declarative base table constraints. Naming of constraints must be consistent. For instance, do not mix system-defined and user-defined names. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
620 Naming of tables Find the names of different types (base table, foreign table, view, materialized view) of tables. Naming of tables must be consistent. For instance, do not mix names in plural and singular form within the same table type. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View