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
401 Duplicate user-defined routines Find user-defined routines with the exact duplicate body and parameters. There should not be multiple routines with exactly the same body and parameters (name, type). Having such duplicates is redundancy. Do remember that the same task can usually be solved in multiple different ways. Thus, the exact copies of routine bodies are not the only possible duplication. Moreover, it could be that different routines that solve the same task have different parameter names (but the parameters have the same types, ordinal positions, default values or the routines have different order of parameters). Thus, the query does not find all the duplications. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-05 11:59 MIT License View
402 Duplicate views Find views with exactly the same subquery. There should not be multiple views with exactly the same subquery. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
403 Duplication of case insensitivity specification in a regular expression Find regular expressions that use both case insensitive search operator ~* and case insensitivity modifier (?i). Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-04 12:49 MIT License View
404 Duplication of parent table CHECK constraints on the foreign key columns Find duplicate constraints, which make it more difficult to maintain the constraints. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
405 Duplication of simple CHECK constraints on the same column Find duplication of simple CHECK constraints on the same base table or foreign table column. Duplication of the same constraint means that if one starts to manage the code, then changes have to be made in multiple places. The problem is essentially similar with the data redundancy problem that database normalization tries to reduce. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
406 Each table is both referencing and referenced table (perhaps there is a cycle in relationships) Find as to whether the relationships between tables form a complete bidirected graph. Tables are vertices in the graph. There is a directed edge between two vertices if one of the tables refers to another through foreign key relationship on mandatory columns. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-07-01 14:23 MIT License View
407 Empty columns Find columns in non-empty tables that do not contain any values. If there are no values in a columns, then it may mean that one hasn't tested constraints that have been declared to the column or implemented by using triggers. It could also mean that such columns are not needed at all. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
408 Empty schemas Find schemas without schema objects. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-04 15:30 MIT License View
409 Empty tables Find base tables where the number of rows is zero. If there are no rows in a table, then it may mean that one hasn't tested constraints that have been declared to the table or implemented by using triggers. It could also mean that the table is not needed because there is no data that should be registered in the table. Problem detection system catalog base tables only 2022-10-21 15:55 MIT License View
410 Enumerated or range types with the same name in different schemas Find cases where in different schemas there are enumerated or range types with the same name. Types are like words that can be used to construct generalized claims about the real world (table predicates). Better not to duplicate the words in the dictionary. Also make sure that this is not a duplication. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
411 Enumerated types with zero or one value Fidn enumerated types with zero or one value. Type is a named finite set of values. The empty set is a set. A set with one value is a set. Thus, types with zero or one value are legal. In practical terms each type, usually, should contain at least two values. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
412 Excessive privileges on databases, schemas, domains, types, languages, foreign data wrappers, and foreign servers Find excessive privileges on databases, schemas, domains, collations, sequences, foreign data wrappers, and foreign servers that are probably not needed by a typical application. Problem detection system catalog base tables only 2021-12-31 14:40 MIT License View
413 Exclude constraint instead of simple UNIQUE Find exclude constraints that implement a simple UNIQUE constraint. The checking might be slower compared to UNIQUE constraint. Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View
414 Explicit locking is missing PostgreSQL uses multiversion concurrency control (MVCC). SELECTs do not block modifications and vice versa. One has to take steps to achieve correct behaviour. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-13 14:57 MIT License View
415 Explicit locking is missing (2) Find user-defined routines that have a subquery in a DELETE or UPDATE statement without the FOR UPDATE clause. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-06 12:15 MIT License View
416 Explicit locking is probably not needed You do not need explicit locking (LOCK TABLE or SELECT … FOR UPDATE) in case of routines that only search some data but do not modify any data and do not raise any exception. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-04 12:08 MIT License View
417 Extension routines in the schema "public" Find extensions that routines are in the schema public. Problem detection system catalog base tables only 2025-06-27 13:01 MIT License View
418 Extension routines that execution privilege has been granted to PUBLIC Know the privileges that users have in your system. Probably all the database users do not need these privileges. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
419 Extreme contraction Find names that contain extremely short terms, due to an excessive word contraction, abbreviation, or acronym usage. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-21 17:20 MIT License View
420 Field size is not sufficiently different in case of base table columns Find base table columns that name refers to the possibility that these are used to register names. Find base table columns that name refers to the possibility that these are used to register comments/descriptions/etc. Find the cases where a base table contains columns from the both sets and the field size in case of the latter is not at least twice as big as in case of the former. For example, if the name of a service can be 100 character long, then it is quite illogical that the description of the service is only at most 200 characters long. Problem detection INFORMATION_SCHEMA only 2021-03-27 18:51 MIT License View