PostgreSQL插入时变量为null自动使用列默认值的解决方法咨询

错误原因 #

DEFAULT是PostgreSQL专用的SQL语法关键字，仅允许直接出现在INSERT语句的VALUES列表中，不能作为表达式的操作数使用，因此嵌套在coalesce函数内会触发语法错误。

解决方案 #

不需要编写两条完全独立的INSERT语句，有三种常用实现方式：

方案1：提前查询字段默认值合并后插入 #

先从系统表查询字段的默认值存入变量，再用coalesce处理后插入，适合表结构固定的场景：

do $$declare
  v text;
  v_c_default text;
begin
  -- 查询表t的c字段默认值
  select pg_get_expr(adbin, adrelid) into v_c_default
  from pg_attrdef
  where adrelid = 't'::regclass
    and adnum = (select attnum from pg_attribute where attrelid = 't'::regclass and attname = 'c');

  insert into t (c) values (coalesce(v, v_c_default));
end$$;

方案2：条件分支控制插入逻辑 #

代码逻辑直观，性能更高，无需查询系统表：

do $$declare
  v text;
begin
  if v is null then
    insert into t (c) values (default);
  else
    insert into t (c) values (v);
  end if;
end$$;

方案3：动态构造插入语句，零重复代码 #

利用format函数动态生成SQL，不需要重复编写INSERT逻辑：

do $$declare
  v text;
begin
  execute format('insert into t (%s) values (%L)', 
    case when v is not null then 'c' end,
    v
  );
end$$;

原理：当变量v为null时，case判断返回null，format函数会忽略null参数，最终生成的SQL为insert into t () values ()，PostgreSQL会自动为所有未指定的字段填充默认值，完全符合需求。

内容的提问来源于stack exchange，提问作者ceving