Query goal: | Domains 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. |
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: | Rename a domain. In case of duplication drop the duplicates and refactor the database so that the remaining domain will be used everywhere it is needed. |
Data source: | INFORMATION_SCHEMA only |
SQL query: | Click on query to copy it
WITH domains AS (SELECT domain_schema, domain_name FROM information_schema.domains WHERE domain_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL)) SELECT domain_name, string_agg(domain_schema,';<br>' ORDER BY domain_schema) AS schemas, Count(*) AS number_of_domains FROM domains GROUP BY domain_name HAVING Count(*)>1 ORDER BY domain_name, Count(*) DESC; |
SQL query | Description |
---|---|
WITH domains AS (SELECT domain_schema, domain_name FROM information_schema.domains WHERE domain_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL)), duplicates AS (SELECT domain_name FROM domains GROUP BY domain_name HAVING Count(*)>1) SELECT format('DROP DOMAIN %1$I.%2$I;', domain_schema, domain_name) AS statements FROM domains WHERE domain_name IN (SELECT domain_name FROM duplicates) ORDER BY domain_schema, domain_name; | Drop the domain. |
Collection name | Collection description |
---|---|
Find problems automatically | Queries, 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 . |
Category name | Category description |
---|---|
Comfortability of database evolution | Queries of this category provide information about the means that influence database evolution. |
Domains | Queries of this category provide information about reusable specifications of column properties. |
Duplication of implementation elements | Queries of this catergory provide information about the duplication of the database objects. |
Naming | Queries of this category provide information about the style of naming. |
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) |