PostgreSQL Upsert函数插入时部分字段为NULL的无额外参数解决方案咨询

当前你的upsert函数在插入新记录时，registrationdate、subscriptionend、stat三个字段会被设为NULL，但更新操作正常。原因是INSERT语句未给这三个字段赋值，且表结构中它们没有默认值约束。以下是两种无需新增参数的解决方法：

方法一：在INSERT语句中直接指定字段值 #

修改INSERT部分，显式为三个字段设置初始值，逻辑可与更新操作保持一致：

CREATE OR REPLACE FUNCTION upsert(
    uname character varying(55),
    fname character varying(55),
    eml character varying(255),
    psw character varying(265),
    phonenbr character varying(55),
    adrs character varying(300)
) 
RETURNS table (j json) AS
$$
BEGIN
INSERT INTO users (id_user, username, firstname, email, password, phonenumber, address, 
                   registrationdate, subscriptionend, stat)
    VALUES (DEFAULT, uname, fname, eml, psw, phonenbr, adrs,
            current_timestamp, current_timestamp + INTERVAL '1 month', 'active')
    ON CONFLICT (username, firstname) 
    DO 
       UPDATE SET email = EXCLUDED.email, password = EXCLUDED.password, phonenumber = EXCLUDED.phonenumber,
                  address = EXCLUDED.address, registrationdate = current_timestamp, 
                  subscriptionend = current_timestamp + INTERVAL '1 month', stat = 'active';
END
$$ 
LANGUAGE plpgsql;

提示：UPDATE中无需重复更新username和firstname，这两个是冲突主键，本身不会发生变化，去掉可优化执行效率。

方法二：给表字段添加默认值约束 #

如果新用户的这三个字段初始值是固定规则（比如注册时间默认当前时间、订阅默认1个月后、状态默认active），可以直接在表层面设置默认值，这样所有插入操作（包括该upsert函数）都会自动应用默认值：

首先修改表结构添加默认值：

ALTER TABLE users
ALTER COLUMN registrationdate SET DEFAULT current_timestamp,
ALTER COLUMN subscriptionend SET DEFAULT current_timestamp + INTERVAL '1 month',
ALTER COLUMN stat SET DEFAULT 'active'::status;

然后简化upsert函数的INSERT语句：

CREATE OR REPLACE FUNCTION upsert(
    uname character varying(55),
    fname character varying(55),
    eml character varying(255),
    psw character varying(265),
    phonenbr character varying(55),
    adrs character varying(300)
) 
RETURNS table (j json) AS
$$
BEGIN
INSERT INTO users (username, firstname, email, password, phonenumber, address)
    VALUES (uname, fname, eml, psw, phonenbr, adrs)
    ON CONFLICT (username, firstname) 
    DO 
       UPDATE SET email = EXCLUDED.email, password = EXCLUDED.password, phonenumber = EXCLUDED.phonenumber,
                  address = EXCLUDED.address, registrationdate = current_timestamp, 
                  subscriptionend = current_timestamp + INTERVAL '1 month', stat = 'active';
END
$$ 
LANGUAGE plpgsql;

这种方法更优雅，将默认值逻辑统一放在表结构中，避免代码重复，后续其他插入操作也能直接受益。

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