PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1041 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
281 Base tables where uniqueness is achieved by using only unique indexes Find base tables where uniqueness is achieved by using only unique indexes, i.e., there is at least one unique index but no uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
282 Base tables, which statistics is probably not up to date Find base tables where statistics has not been collected at all or it has been lastly collected more than 40 days ago. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
283 Base tables with exactly one key Find all base tables that have exactly one PRIMARY KEY or UNIQUE constraint. Find and enforce all the keys. Are you sure there are not more keys in the table? General system catalog base tables only 2025-11-07 10:11 MIT License View
284 Base tables with multiple Boolean columns Find base tables that have more than one column with Boolean type. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
285 Base tables with multiple comment columns Find base tables with more than one comment columns General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
286 Base tables with multiple name columns Find base tables with more than one name columns. Perhaps the normalization level of the table is low. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
287 Base tables with multiple temporal columns Find base tables that have more than one column with a temporal type (date or timestamp). General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
288 Base tables with plenty of data Find base tables that have 1000 rows or more. General system catalog base tables only 2025-11-07 10:11 MIT License View
289 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 2025-11-07 10:11 MIT License View
290 BOOLEAN base table and foreign table columns with a CHECK constraint that involves olnly this column Find base table and foreign table columns with the Boolean type that has a CHECK constraint that involves only this column. Avoid unnecessary CHECK constraints. The Boolean type contains only two values and there is nothing to check. By creating a check that determines that possible values in the column are TRUE and FALSE, one duplicates the attribute constraint (column has a type). This is a form of duplication. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
291 BOOLEAN base table and foreign table columns with a PRIMARY KEY, UNIQUE, or FOREIGN KEY constraint that involves olnly this column Find base table columns with the Boolean type that has a PRIMARY KEY, UNIQUE, or FOREIGN KEY constraint that involves only this column. Avoid unnecessary constraints. It is quite improbable that there must be such constraints. For instance, a table with PRIMARY KEY () or UNIQUE () constraint can have at most two rows. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
292 Boolean column for gender Find base table columns that have Boolean type and based on the column name are meant for recording data about gender. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
293 Candidate key columns that have a static default value Find base table columns that are covered by a primary key or a unique constraint and that probably have a static default value. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
294 Candidate keys and foreign keys of tables that participate in an inheritance hierarchies Find primary key, unique, foreign key, and exclude constraints that have been defined in tables that participate in an inheritance hierarchy. Do not forget to redefine the constraints that are defined on supertables also on their subtables. General system catalog base tables only 2025-11-07 10:11 MIT License View
295 Candidate keys where all columns have a static default value Find base table primary key and unique constraints where all columns probably have a static default value. Candidate key columns (columns that belong to the primary key or to an unique constraints) shouldn't have static default values unless you are enforcing a rule that a table can have at most one row. The point of default values is that system assigns them automatically. There cannot be multiple rows with the same key value. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
296 Cannot accommodate all the fractional seconds in case of table columns The precision of a timestamp type of a column must be able to accommodate all the fractional seconds of the default value of the column. Find table columns with the type timestamp without time zone(m) or timestamp with time zone(m) that have a default value LOCALTIMESTAMP(n) or CURRENT_TIMESTAMP(n) WHERE n>m. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
297 Cannot register all legal e-mail addresses Find CHECK constraints on base table or foreign table columns that contain data about e-mail addresses and apply unnecessary restrictions to the these, rejecting potentially some legal addresses. More precisely, find CHECK constraints that prevent registration of e-mail addresses with multiple @ signs. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
298 Cannot register all legal personal names Find CHECK constraints on base table or foreign table columns that contain data about personal names and apply unnecessary restrictions to the names, rejecting potentially some legal names. Find checks that prohibit a digit or require a letter A-Z. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
299 Cascading update is not needed (based on surrogate keys) Find foreign key constraints that reference to a candidate key that is a surrogate key, i.e., its values are generated by the system by using sequence generators. Do not use ON UPDATE CASCADE, ON UPDATE SET NULL, and ON UPDATE SET DEFAULT in case of foreign keys that reference to surrogate keys. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
300 Case insensitive search Refers to the column pg_proc.prokind and thus works starting from PostgreSQL 11. Find user-defined routines and derived tables (views/materialized views) that have a subquery with case insensitive search (by using the upper or lower function or ILIKE predicate or (?i) modifier of a regular expression pattern). General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View