且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

在Postgres中有条件地将列设置为其默认值

更新时间:2022-01-02 23:25:16

使用此方法:

update mytable set a = 
    coalesce(incidentid, 
    (
     select column_default::int 
     from information_schema.columns 
     where table_schema = 'public' 
     and table_name = 'mytable' and column_name = 'incidentid')
    )

如果您的事件ID是整数类型,请在column_default上输入类型转换

if your incidentid is integer type, put a typecast on column_default

create or replace function get_default_value(_table_name text,_column_name text) 
returns text
as
$$
declare r record;
s text;
begin

    s = 'SELECT ' || coalesce(

    (select column_default 
    from information_schema.columns 
    where table_schema = 'public' 
    and table_name = _table_name and column_name = _column_name)

    , 'NULL') || ' as v';

    EXECUTE s into r;
    return r.v;
end;
$$
language 'plpgsql';

要使用:

update mytable set a = 
    coalesce(incidentid, get_default_value('mytable','a')::int )