The list of all the queries

Base table columns with the same name and type have different field sizes

Query goal: Find base table columns that have the same name and type but different field size.
Query type: Problem detection (Each row in the result could represent a flaw in the design)
Query reliability: Low (Many false-positive results)
Query license: MIT License
Data source: INFORMATION_SCHEMA only
SQL query: Click on query to copy it

WITH data_types AS (SELECT table_schema, table_name, column_name, data_type,
CASE WHEN data_type='numeric' AND numeric_precision IS NOT NULL AND numeric_scale IS NOT NULL THEN data_type || '(' || numeric_precision || ',' || numeric_scale || ')'
WHEN character_maximum_length IS NOT NULL THEN data_type || '(' ||  character_maximum_length || ')'
WHEN datetime_precision IS NOT NULL AND data_type<>'date' THEN data_type || '(' ||  datetime_precision || ')'
ELSE data_type END AS precision_spec
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, data_type, Count(*) AS number_of_columns, Count(DISTINCT precision_spec) AS number_of_different_sizes,
string_agg(table_schema ||'.'|| table_name || ' ' || precision_spec,';<br>' ORDER BY precision_spec, table_schema, table_name) AS precisions
FROM data_types AS dt
GROUP BY column_name, data_type
HAVING Count(DISTINCT precision_spec)>1
ORDER BY Count(DISTINCT precision_spec) DESC, Count(*) DESC, column_name;

Collections where the query belongs to

Collection nameCollection description
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

Categories where the query belongs to

Category nameCategory description
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.
Field sizeQueries of this category provide information about the maximum size of values that can be recorded in column fields
InconsistenciesQueries of this catergory provide information about inconsistencies of solving the same problem in different places.

The list of all the queries