在Postgres中有条件地将列设置为其默认值 [英] Conditionally set a column to its default value in Postgres

问题描述

我有一个PostgreSQL 8.4表，该表具有自动递增但可为空的整数列。我想更新一些列值，如果此列为NULL，则将其设置为其默认值（这是从序列自动生成的整数），但是 我想返回其值无论哪种情况。所以我想要这样的东西：

I've got a PostgreSQL 8.4 table with an auto-incrementing, but nullable, integer column. I want to update some column values and, if this column is NULL then set it to its default value (which would be an integer auto-generated from a sequence), but I want to return its value in either case. So I want something like this:

UPDATE mytable
SET incident_id = COALESCE(incident_id, DEFAULT), other = 'somethingelse'
WHERE ...
RETURNING incident_id

不幸的是，这没有有用-似乎 DEFAULT 很特殊，不能成为表达式的一部分。最好的方法是什么？

Unfortunately, this doesn't work - it seems that DEFAULT is special and cannot be part of an expression. What's the best way to do this?

推荐答案

使用此方法：

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 )

这篇关于在Postgres中有条件地将列设置为其默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！