postgresql - Postgres : COALESCE all columns

Question

我正在处理一个包含太多列的表格，不利于良好的设计。 (接近 100)。我无法更改架构。

我需要在 NULL 上将每一列合并为 0。有没有一种方法可以在不手动输入所有 100 列的情况下执行此操作？ (也许使用某种类型的数据字典或元信息？)

Answer 1

您可以创建一个 Postgres 函数来实现所需的输出。

示例表和数据 -

create table t(id serial, col1 int, col2 int, col3 int, col4 int, col5 int, col6 int, coln int); 
insert into t values ( 1, 1, 2, null,null,null,1),(1, null, null, null,2,null,6);

以下 select 语句将创建动态 select 以使用 coalesce() 获取所有表。

SELECT format('select %s from t', string_agg(format('coalesce(%s,0)', column_name), ',')) q
FROM information_schema.columns
WHERE table_schema = 'public'
    AND table_name = 't'

结果:

q                                                                                                                                                   
--------------------------------------------------------------------------------------------------------------------------------------------------- 
select coalesce(id,0),coalesce(col1,0),coalesce(col2,0),coalesce(col3,0),coalesce(col4,0),coalesce(col5,0),coalesce(col6,0),coalesce(coln,0) from t 

(1 row(s) affected)

你可以把它包装成一个函数

 create or replace function get_table_t ()
returns setof t as $$
declare qry text;
begin
    SELECT format('select %s from t', string_agg(format('coalesce(%s,0)', column_name), ','))
    into qry
    FROM information_schema.columns
    WHERE table_schema = 'public'
        AND table_name = 't'; 
    return query

    execute qry;
end;$$
LANGUAGE plpgsql VOLATILE

用法:从 get_table_t() 中选择 *

输出:

id col1 col2 col3 col4 col5 col6 coln 
-- ---- ---- ---- ---- ---- ---- ---- 
1  1    2    0    0    0    1    0    
1  0    0    0    2    0    6    0