The list of all the queries

Duplicate rules

Query goal: Find multiple rules with the same definition (event, condition, action) on the same table. 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.
Notes about the query: In case of the string_agg function, the line break (br) tag is used as a part of the separator for the better readability in case the query result is displayed in a web browser.
Query type: Problem detection (Each row in the result could represent a flaw in the design)
Query reliability: High (Few or no false-positive results)
Query license: MIT License
Fixing suggestion: All but one are redundant. Drop the redundant rules.
Data source: system catalog only
SQL query: Click on query to copy it

SELECT schemaname, tablename, replace(definition, rulename, 'rulename') AS rule_definition, 
string_agg(rulename, ';<br>' ORDER BY rulename) AS rules,
Count(*)  AS number_of_rules
FROM pg_rules
GROUP BY schemaname, tablename,  rule_definition
HAVING Count(*)>1
ORDER BY schemaname, tablename, Count(*) DESC;

SQL statements for generating SQL statements that help us to fix the problem

SQL queryDescription
WITH duplicate_rules AS (
SELECT schemaname, tablename, array_agg(rulename) AS rules_array
FROM pg_rules
GROUP BY schemaname, tablename,  replace(definition, rulename, 'rulename')
HAVING Count(*)>1)
SELECT format('DROP RULE %1$I ON TABLE %2$I.%3$I;', unnest(rules_array),  schemaname, tablename) AS statements
FROM duplicate_rules 
ORDER BY schemaname, tablename, unnest(rules_array);
Drop the rule. One of the rules must stay in place.

Collections where the query belongs to

Collection nameCollection description
Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

Categories where the query belongs to

Category nameCategory description
Duplication of implementation elementsQueries of this catergory provide information about the duplication of the database objects.
Triggers and rulesQueries of this category provide information about triggers and rules in a database.

Reference materials for further reading

Reference
https://refactoring.guru/smells/alternative-classes-with-different-interfaces
https://refactoring.guru/smells/duplicate-code
The corresponding code smell in case of cleaning code is "G5: Duplication". (Robert C. Martin, Clean Code)

The list of all the queries