Goal This query identifies semantic inconsistencies across the database schema by finding base table columns that share the same identifier (name) but are defined with differing data types. According to standard data modeling principles, a shared attribute name implies a shared domain concept (e.g., status_code should consistently be an SMALLINT or a CHAR). Discrepancies in data types for homonymous columns (e.g., is_active being BOOLEAN in one table and SMALLINT in another) hinder interoperability, complicate join logic, and confuse developers.
Notes 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.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Medium (Medium number of false-positive results)
License MIT (opens in new tab)
Fixing Suggestion Change the name of the column or the type of the column to ensure consistency.
Data Source INFORMATION_SCHEMA only
SQL Query 
WITH data_types AS (SELECT table_schema, table_name, column_name, data_type
FROM INFORMATION_SCHEMA.columns
WHERE (table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE') AND 
table_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 column_name, Count(*) AS number_of_columns, Count(DISTINCT data_type) AS number_of_different_types,
string_agg(table_schema ||'.'|| table_name || ' ' || Upper(data_type),';<br>' ORDER BY data_type, table_schema, table_name) AS types
FROM data_types AS dt
GROUP BY column_name
HAVING Count(DISTINCT data_type)>1
ORDER BY Count(DISTINCT data_type) DESC, Count(*) DESC, column_name;

Collections

This query belongs to the following collections:

Find problems about base tables

A selection of queries that return information about the data types, field sizes, default values as well as general structure of base tables. Contains all the types of queries - problem detection, software measure, and general overview

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 .

NameDescription
Find problems about base tablesA selection of queries that return information about the data types, field sizes, default values as well as general structure of base tables. Contains all the types of queries - problem detection, software measure, and general overview
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

This query is classified under the following categories:

Comfortability of data management

Queries of this category provide information about the means that have been used to make the use or management of database more comfortable and thus, more efficient.

Data types

Queries of this category provide information about the data types and their usage.

Inconsistencies

Queries of this catergory provide information about inconsistencies of solving the same problem in different places.

Naming

Queries of this category provide information about the style of naming.

NameDescription
Comfortability of data managementQueries of this category provide information about the means that have been used to make the use or management of database more comfortable and thus, more efficient.
Data typesQueries of this category provide information about the data types and their usage.
InconsistenciesQueries of this catergory provide information about inconsistencies of solving the same problem in different places.
NamingQueries of this category provide information about the style of naming.

Further reading and related materials:

The corresponding code smell in case of cleaning code is "G11: Inconsistency". (Robert C. Martin, Clean Code)
Smell "Overloaded attribute names": Sharma, T., Fragkoulis, M., Rizou, S., Bruntink, M. and Spinellis, D.: Smelly relations: measuring and understanding database schema quality. In: Proceedings of the 40th International Conference on Software Engineering: Software Engineering in Practice, pp. 55-64. ACM, (2018).
Factor, P.: SQL Code Smells. Redgate, http://assets.red-gate.com/community/books/sql-code-smells.pdf, last accessed 2019/12/29 (Using the same column name in different tables but with different data types)
Reference
The corresponding code smell in case of cleaning code is "G11: Inconsistency". (Robert C. Martin, Clean Code)
Smell "Overloaded attribute names": Sharma, T., Fragkoulis, M., Rizou, S., Bruntink, M. and Spinellis, D.: Smelly relations: measuring and understanding database schema quality. In: Proceedings of the 40th International Conference on Software Engineering: Software Engineering in Practice, pp. 55-64. ACM, (2018).
Factor, P.: SQL Code Smells. Redgate, http://assets.red-gate.com/community/books/sql-code-smells.pdf, last accessed 2019/12/29 (Using the same column name in different tables but with different data types)