代码之家 › 专栏 › 技术社区 › ROBERT RICHARDSON

有没有一种简单的方法可以用默认值替换空值?

postgresql

ROBERT RICHARDSON · 技术社区 · 6 年前

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

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_defaults.value2的值20?

2 回复 | 直到 6 年前

klin 6 年前

示例表:

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)

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.

ROBERT RICHARDSON 6 年前

create or replace function conversion.copy_table_with_defaults_ex(schema_from text, table_from text, schema_to text, table_to text)
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
    left join pg_class c on c.oid = a.attrelid
    left join pg_namespace ns on c.relnamespace = ns.oid
    where attnum > 0
    and c.relname = table_to
    and ns.nspname = schema_to
    into column_list;

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