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
541 User-defined routines with the same parameters (same name and type) regardless of the order of parameters Find routines with the same parameters (same name and type) regardless of the order of parameters. Make sure that there is no accidental duplication. The query helps users to group together routines that probably have related tasks. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
542 Perhaps a reference to the variable OLD is missing Find row level before delete triggers that only task is not to raise an exception and where the variable OLD is not used in the trigger routine outside the RETURN clause. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
543 Perhaps a reference to the variable NEW is missing Find row level before insert and before update triggers that only task is not to raise an exception and where the variable NEW is not used in the trigger routine outside the RETURN clause. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
544 ROW level BEFORE triggers that do not return a row if a check succeeds Find ROW level BEFORE triggers that check a condition based on other rows, raise an exception but do not return the row if the condition check succeeds, i.e., exception is not raised. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
545 ROW level BEFORE UPDATE triggers that do not return the new row Find row level BEFORE UPDATE triggers that do not return the new row version. Exclude triggers that raise WARNING/EXCEPTION. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
546 Perhaps incorrect WHEN clause Find row level triggers that have action condition (WHEN clause) but the Boolean expression in its specifications does not refer to neither NEW nor OLD variable. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
547 Incorrect password hash update Find row level update triggers that incorrectly implement update of password hash. It should not be that the new password hash is calculated based on the existing hash. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
548 Rules with the same name in different schemas Find rule names that are used in a database in more than one schema. Different things should have different names. But here different rules have the same name. Also make sure that this is not a duplication. General system catalog base tables only 2025-11-07 10:11 MIT License View
549 PUBLIC has the USAGE privilege of a schema Find schemas where PUBLIC has the usage privilege. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
550 All non-unique indexes Find secondary indexes that have been created in the database. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
551 SECURITY INVOKER routines that access data Find SECURITY INVOKER routines that read rows from a table, add rows to a table, update rows in a table, or delete rows from a table. Better to have for these purposes SECURITY DEFINER routines, which make it possible to give to the users privileges to only execute routines without having rights to access their underlying tables. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
552 Potentially unused sequence generators Find sequence generators that are not associated with any column through the default value mechanism. Please note, that it is also possible to refer to a sequence generator from a routine or from an application. If these are indeed not used, then these should be dropped, otherwise these are dead code. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
553 Sequences that are not owned by a table column Find sequence generators that are not owned by a table column, i.e., if one drops the table or the column, then the sequence generator stays in place. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
554 Case sensitive and case insensitive uniqueness of the same simple key Find sets of columns that have both case sensitive and case insesitive unique constraints enforced based on these columns. In case of textual columns uniqueness could be checked either in case sensitive or case insensitive way. It is a contradiction to check the uniqueneness in both ways in case of the same key. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
555 Simple check constraints with multiple tasks Find simple check constraints, i.e., check constraints that cover one column that seem to have multiple tasks. The corresponding code smell in case of cleaning code is "G30: Functions Should Do One Thing". (Robert C. Martin, Clean Code) Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
556 CHECKs are associated with a column instead of the domain of the column Find simple check constraints (involve one column) that are associated with a base table column instead of the domain of the column. Common checks of data in columns that share the same domain should be described at the level of domain not at the level of columns. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
557 Potentially unnecessary use of sequence generators Find simple natural key columns that are associated with a sequence generator. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
558 Simple primary keys that column name does not contain the table name Find simple primary keys that column name does not contain the table name. The naming should be clear and consistent. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
559 SQL function does not return a value Find SQL functions that do not return a value (return VOID) but the SQL statement in the function has RETURNING clause. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
560 Multiple RETURNING clauses in a SQL function Find SQL functions that have multiple statements with the RETURNING clause. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View