WITH columns AS (SELECT c.table_schema, c.table_name, c.column_name, c.ordinal_position, translate(c.column_name,'0123456789','') AS column_name_stripped, 
CASE WHEN data_type='numeric' 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 data_type
FROM INFORMATION_SCHEMA.columns AS c INNER JOIN INFORMATION_SCHEMA.tables AS t
USING (table_schema, table_name)
WHERE t.table_type='BASE TABLE' AND 
c.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)
AND column_name=regexp_replace(column_name, '(_[[:digit:]]{2}$|_[[:digit:]]{4}$)', '', 'g')),
cloned as (SELECT table_schema, table_name, data_type, string_agg(column_name, ',' ORDER BY ordinal_position) AS columns, Count(*) AS number_of_columns
FROM columns
GROUP BY table_schema, table_name, column_name_stripped, data_type
HAVING Count(*)>1)
SELECT table_schema, table_name, Count(*) AS number_of_groups, Sum(number_of_columns) AS number_of_columns, string_agg(columns || ' ' || data_type, ';
') AS all_columns
FROM cloned
GROUP BY table_schema, table_name
ORDER BY Sum(number_of_columns) DESC, Count(*) DESC, table_schema, table_name;