database - 是否有一个脚本可以规范创建不佳的数据库表？

如果它很小，那么您可以编写以下查询：

select serial_number, number1 from bad_table
union all
select serial_number, number2 from bad_table
union all
select serial_number, number3 from bad_table
;

如果您不想复制空值，请添加not null限制：

select serial_number, number1 from bad_table where number1 is not null
union all
select serial_number, number2 from bad_table where number2 is not null
union all
select serial_number, number3 from bad_table where number3 is not null
;

如果它比这个大，那么你可以从information_schema.columns.

with queries as (
  select concat('select serial_number, ',
                column_name,
                ' from bad_table where ',
                column_name,
                ' is not null') as query
    from information_schema.columns
   where table_name = 'bad_table'
     and column_name like 'number%'
), asarray as (
  select array_agg(query) as qs
    from queries
)
select array_to_string(qs, ' union all ')
  from asarray;

这可能需要一些简化，但这是一个开始：

with tname as (                                                                                                                                                                                                               select 'bad_table' as t                                                                                                                                                                                                              ), stemmed_columns as (
  select column_name::text as column_name, 
         regexp_replace(column_name, '\d+$', '') as rootname, 
         regexp_replace(column_name, '^\D+', '') as ind
    from information_schema.columns 
   cross join tname 
   where table_name = tname.t
), col_arrays as (
  select array_to_string(
           array_agg(column_name) 
             filter (where ind = ''),
           ', '
         ) as leadcols,
         array_agg(column_name order by column_name)
           filter (where ind != '')
         as icols,
         array_agg(distinct rootname order by rootname) 
           filter (where ind != '')
          as stems 
    from stemmed_columns
), index_limits as (
  select distinct ind::int as indint
    from stemmed_columns
   where ind != ''
), blowout as (
  select l.indint, 
         array_agg(
           case 
             when (s.stem||l.indint::text) = any(a.icols) then s.stem||l.indint::text
             else 'null' 
           end || ' as '|| s.stem order by s.stem) as srcname,
         array_agg(
           case 
             when (s.stem||l.indint::text) = any(a.icols) then s.stem||l.indint::text
             else 'null' 
           end order by s.stem) as srcnameonly
    from index_limits l
   cross join col_arrays a
   cross join (select unnest(stems) as stem from col_arrays) s
   group by l.indint
), qlines as (
  select concat(
           'select ', a.leadcols, ', ',
           array_to_string(b.srcname, ', '),
           ' from ', 
           tname.t,
           ' where coalesce(',
           array_to_string(b.srcnameonly, ', '),
           ') is not null'
         ) as qline 
    from blowout b
   cross join col_arrays a
   cross join tname
)
select array_to_string(array_agg(qline), ' union all ') as query
  from qlines;

database - 是否有一个脚本可以规范创建不佳的数据库表？

问题描述

解决方案

推荐阅读