postgres upsert的部分更新违反了约束条件

问题描述：

我希望能够在postgres（9.5）中部分地插入部分内容，但似乎在部分upsert未满足所有约束条件时会失败（例如非空约束条件）postgres upsert的部分更新违反了约束条件

这里的场景和错误

CREATE TABLE jobs (
    id integer PRIMARY KEY, 
    employee_name TEXT NOT NULL, 
    address TEXT NOT NULL, 
    phone_number TEXT 
); 

CREATE OR REPLACE FUNCTION upsert_job(job JSONB) 
RETURNS VOID AS $$ 
BEGIN 
INSERT INTO jobs AS origin VALUES(
    (job->>'id')::INTEGER, 
    job->>'employee_name'::TEXT, 
    job->>'address'::TEXT, 
    job->>'phone_number'::TEXT 
) ON CONFLICT (id) DO UPDATE SET 
    employee_name = COALESCE(EXCLUDED.employee_name, origin.employee_name), 
    address = COALESCE(EXCLUDED.address, origin.address), 
    phone_number = COALESCE(EXCLUDED.phone_number, origin.phone_number); 
END; 
$$ LANGUAGE PLPGSQL SECURITY DEFINER; 


--Full insert (OK) 
SELECT upsert_job('{"id" : 1, "employee_name" : "AAA", "address" : "City, x street no.y", "phone_number" : "123456789"}'::jsonb); 

--Partial update that fulfills constraint (Ok) 
SELECT upsert_job('{"id" : 1, "employee_name" : "BBB", "address" : "City, x street no.y"}'::jsonb); 

--Partial update that doesn't fulfill constraint (FAILS) 
SELECT upsert_job('{"id" : 1, "phone_number" : "12345"}'::jsonb); 

--ERROR: null value in column "employee_name" violates not-null constraint 
--DETAIL: Failing row contains (1, null, null, 12345).

我怎么到处去接近这个的例子吗？

我不立即看到这里有什么问题。我建议发布到pgsql-general邮件列表以获得更多的目光。这可能是一个错误。如果你确实写信给邮件列表，*请*在你的邮件列表消息中加入这篇文章的链接。 –

答

想想另一种方式，如果id不存在，该怎么办？你不能插入一个电话号码，因为它没有名字/地址，但这正是你要求它做的。所以约束变得疯狂，并且失败，因为一个upsert 试图插入第一个，然后在插入失败时更新。但是你的插入没有通过约束检查来查看它是否已经存在。

你可以做什么，而不是如果你想partials是告诉它如何处理违反约束的部分。像这样的东西（这是不完整的，不处理所有部分数据场景）：

CREATE OR REPLACE FUNCTION upsert_job(job JSONB) 
RETURNS VOID AS $$ 
BEGIN 
IF (job->>'phone_number' IS NOT NULL 
    AND job->>'employee_name' IS NOT NULL 
    AND job->>'address' IS NOT NULL) THEN 
    INSERT INTO jobs AS origin VALUES(
     (job->>'id')::INTEGER, 
     job->>'employee_name'::TEXT, 
     job->>'address'::TEXT, 
     job->>'phone_number'::TEXT 
    ) ON CONFLICT (id) DO UPDATE SET 
     employee_name = COALESCE(EXCLUDED.employee_name, origin.employee_name), 
     address = COALESCE(EXCLUDED.address, origin.address), 
     phone_number = COALESCE(EXCLUDED.phone_number, origin.phone_number); 
ELSIF (job->>'phone_number' IS NOT NULL AND (job->>'employee_name' IS NULL AND job->>'address' IS NULL)) THEN 
    UPDATE jobs SET phone_number=job->>'phone_number'::TEXT 
    WHERE id=(job->>'id')::INTEGER; 
END IF; 

END; 
$$ LANGUAGE PLPGSQL SECURITY DEFINER;

postgres upsert的部分更新违反了约束条件

相关推荐