postgresql - 有没有一种简单的方法可以将 null 替换为默认值？

Question

我想将数据从一个表复制到另一个表，但源表的列中可能有空值，而目标表不允许空值但具有默认值:

drop table if exists table_with_nulls;
create table table_with_nulls
(
    value1 integer,
    value2 integer
);
insert into table_with_nulls values (1, null);
drop table if exists table_with_defaults;
create table table_with_defaults
(
    value1 integer not null default 10,
    value2 integer not null default 20
);
insert into table_with_defaults (value1, value2)
select value1, value2 from table_with_nulls;

由于 table_with_nulls.value2 中的空值而引发异常。有没有一种相当简单的方法可以让 table_with_defaults.value2 的值为 20？

Answer 1

示例表:

create table table_with_defaults
(
    value1 integer not null default 10,
    value2 numeric not null default 0.0,
    value3 text not null default '-- nothing --',
    value4 date not null default current_date,
    value5 text
);

可以查询系统目录pg_attribute和 pg_attrdef查找表列的默认表达式:

select attname, adsrc
from pg_attribute a
left join pg_attrdef d on adrelid = attrelid and adnum = attnum
where attnum > 0
and attrelid = 'table_with_defaults'::regclass;

 attname |         adsrc         
---------+-----------------------
 value1  | 10
 value2  | 0.0
 value3  | '-- nothing --'::text
 value4  | ('now'::text)::date
 value5  | 
(5 rows)    

在 plpgsql 函数中使用查询来构建和执行适当的语句:

create or replace function copy_table_with_defaults(table_from regclass, table_to regclass)
returns void language plpgsql as $$
declare
    column_list text;
begin

    select string_agg(
        case when adsrc is null then attname 
        else format('coalesce(%I, %s)', attname, adsrc) 
        end, ',')
    from pg_attribute a
    left join pg_attrdef d on adrelid = attrelid and adnum = attnum
    where attnum > 0
    and attrelid = table_to
    into column_list;

    execute format($ex$
        insert into %I
        select %s
        from %I
        $ex$, table_to, column_list, table_from);
end $$;

使用函数:

select copy_table_with_defaults('table_with_nulls'::regclass, 'table_with_defaults'::regclass);

Working example in rextester.