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
681 Tables with the same name in different schemas Find tables with the same name in different schemas. Make sure that this is not a duplication. General INFORMATION_SCHEMA+system catalog base tables 2021-11-10 15:40 MIT License View
682 Username is not unique Find textual columns that potentially contain usernames (including columns that potentially contain e-mail addresses) that do not have a unique constraint or a unique index that involves only this column. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
683 Base tables with the biggest number of rows Find the base tables that belong to the top 5 in terms of the number of rows in the table. There should be test data in the tables. General system catalog base tables only 2022-10-21 01:48 MIT License View
684 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 2022-10-21 01:47 MIT License View
685 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 2021-03-12 11:21 MIT License View
686 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 2022-11-30 13:04 MIT License View
687 Phone number column has an incorrect data type Find the columns where the name reffers to the possibility that the column contains phone numbers but the type of the column is not VARCHAR or TEXT. Problem detection INFORMATION_SCHEMA only 2021-03-27 19:00 MIT License View
688 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 2023-01-10 14:53 MIT License View
689 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 2023-01-10 14:53 MIT License View
690 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 2020-11-06 14:51 MIT License View
691 Different non-surrogate key default values Find the different default values that implement something other than a surrogate key. General INFORMATION_SCHEMA only 2021-03-04 10:31 MIT License View
692 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 2020-12-16 10:59 MIT License View
693 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 2020-11-08 19:15 MIT License View
694 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 2020-11-08 19:16 MIT License View
695 ON UPDATE CASCADE is probably missing (based on the properties of the referenced column) Find the foreign key constraints that do not have ON UPDATE CASCADE and that referenced key is a simple key that has an integer type, is not covered by another foreign key, and does not have an associated sequence generator, i.e., the foreign key references a simple natural key. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-04-30 18:39 MIT License View
696 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 2020-11-14 15:42 MIT License View
697 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 2024-12-21 16:45 MIT License View
698 Names of database objects that perhaps end with a sequence number Find the names (identifiers) of user-defined database objects that end with one or more digit. Names should be informative. Duplicates should be avoided. Digits in names are a possible sign of duplication of database objects or unclear names. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-05-05 07:08 MIT License View
699 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-05-03 13:51 MIT License View
700 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 2023-11-04 15:19 MIT License View