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
441 Names of the columns of derived tables that have been given by the system Find columns of derived tables that name has been given by the system. The creators of the table should specify the name themselves to avoid ugly names and nasty surprises. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-15 16:48 MIT License View
442 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 2022-11-15 16:43 MIT License View
443 Table constraints with the same name (constraints connected directly with a base table or a foreign table) Find base table and foreign table constraint names that are used in a database more than once (possibly in different schemas or in case of different types of constraints). 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 2022-11-15 16:43 MIT License View
444 Prefixes of base table names Find base tables that name starts with a prefix. Do not use prefixes in case of base table names. Derive the names from the names of entity types. Do not use "_", "t_", "tab_", "t11_" etc as prefixes of a table. Problem detection INFORMATION_SCHEMA only 2022-11-15 16:32 MIT License View
445 Inconsistent naming of comment columns Find columns of tables that start with the word comment or komment but end differently (excluding numbers). Return result only if there is more than one naming variant of such columns in the database. For instance, a column has the name "comment" but another "comments". Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-15 14:09 MIT License View
446 Gratuitous context in the names of non-foreign key and non-candidate key columns Find the names on base table columns that are not a part of a candidate key and a foreign key and that contain the name of the table. Exclude very general column names (for instance, nimi, nimetus, kommentaar, kirjeldus, name, comment, description). In case of these using the table name in the column name is not a problem because it simplifies writing the queries based on the tables. In this case one does not have to rename the columns in the query result. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-15 13:39 MIT License View
447 Table inheritance (path view) Find in case of each base table that participates in a table inheritance hierarchy the path to the table from the top-level table. Use table inheritance carefully because, for instance, certain constraints are not inherited and must be redefined on child tables. Also make sure that the identifier of each child table in an inheritance hierarchy is a hyponym of the identifier of its parent table. General system catalog base tables only 2022-11-13 16:13 MIT License View
448 Patterns of the Boolean expressions of simple CHECK constraints Find patterns of the Boolean expressions of simple CHECK constraints (involve only one column). Do not solve the same task in different places differently. The same rule could be implemented with CHECK constraints that have different Boolean expressions. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code) General INFORMATION_SCHEMA+system catalog base tables 2022-11-13 16:06 MIT License View
449 Too generic names regarding persons and their names (columns of derived tables) Find derived table columns that name refers to the possibility that these contain person names. The names of derived table columns that contain person names should refer to the role that the corresponding entity type has in the view. For instance, if view active_product has a column with the name surname, then the column name does not give information what is the role of the person in the context of the view. Better name would be, for instance, registrator_surname. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-10 14:25 MIT License View
450 The number of columns based on table type Find the total number of columns in the different types of tables as well as average number of columns in the tables as well as minimal and maximal number of columns. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2022-11-10 14:12 MIT License View
451 Only ID primary key Find base base tables have the simple primary key that contains a column with the (case insensitive) name id and an integer type. In addition, the primary key values are generated automatically by the system by using a sequence generator. In addition the base table must not have any unique constraint. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-09 15:15 MIT License View
452 Pairs of base tables that have at least two columns with the same names and data types What are the pairs of base tables that have at least two columns with the same names and data types. The tables might violate the principle of orthogonal design and hence might facilitate uncontrolled data redundancy over different tables. Problem detection INFORMATION_SCHEMA only 2022-11-09 13:13 MIT License View
453 Empty schemas Find schemas without schema objects. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-04 15:30 MIT License View
454 All user schemas Find all the schemas in the database that are not used for the system purposes. General INFORMATION_SCHEMA only 2022-11-04 15:14 MIT License View
455 At most one row is permitted in a table (based on check constraints) Find base tables and foreign tables where based on a check constraint, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a check constraint permits only one possible value in a column, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint. General INFORMATION_SCHEMA+system catalog base tables 2022-11-03 15:21 MIT License View
456 At most one row is permitted in a table (based on enumeration types) Find base tables and foreign tables where based on the type of a column, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a column has an enumeration type with exactly one value, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint. General INFORMATION_SCHEMA+system catalog base tables 2022-11-03 15:18 MIT License View
457 Potentially missing PRIMARY KEY or UNIQUE constraints (based on UUIDs) If something has to be unique, then it must be said to the system so that it could use the information for internal optimizations and enforce the constraint. Find columns that contain Universally Unique Identifiers but are not a part of any simple PRIMARY KEY/UNIQUE constraint and are also not part of a foreign key. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-03 11:18 MIT License View
458 Multiple simple keys with integer values Find base tables that have more than one primary key or unique constraints that involve exactly one integer column. Do not overcomplicate the database. Perhaps some column in the table is redundant. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-03 11:15 MIT License View
459 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 2022-11-03 10:46 MIT License View
460 FILLFACTOR is probably too small Too small fillfactor wastes storage space. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-02 10:43 MIT License View