sql - Upsert/on 与串行主键冲突

Question

更新插入在冲突时有效，但 id 的数据类型是串行的，这意味着我想让它自动生成/递增。如果我在不指定 id 的情况下执行插入，则插入工作正常。

我遇到的问题是将两者结合起来。为了让 key 自动递增，我没有将 id 传递到插入中，但如果我不传递 id 那么更新将永远不会触发。我不能将 null 传递给 id，因为它是非空字段。

在下面的示例中 - 我第一次运行查询，它执行插入，第二次执行更新，但我无法弄清楚如何将“无”传递给插入，因此身份 key 在插入时仍然有效。我可以将 DEFAULT 放入插入中，但如果有的话，我就无法传递真实的 id 值。

CREATE TABLE public.upsert_test
(
    id INTEGER NOT NULL DEFAULT nextval('upsert_test_id_seq'::regclass),
    name character varying(20) COLLATE pg_catalog."default",
    description character varying(20) COLLATE pg_catalog."default",
    CONSTRAINT upsert_test_pkey PRIMARY KEY (id)
)

INSERT INTO upsert_test (id, name, description)
VALUES (1, 'thing1', 'test')
on conflict (id)
do update set (name , description) = ('thing_updated','test-updated')   
where upsert_test.id = 1;

Answer 1

您可以更改查询以使用序列函数，例如:

INSERT INTO upsert_test (id, name, description)
VALUES ((select nextval('upsert_test_id_seq')), 'thing1', 'test')
on conflict (id)
do update set (name , description) = ('thing_updated','test-updated')   
where upsert_test.id = (select currval('upsert_test_id_seq'));

请注意，这可能不是线程安全的，例如，如果在第一次调用 select currval('upsert_test_id_seq') 之前执行此 sql 的第二次调用，则更新可能会在第一次查询中失败。

从 op 获得更多信息后更新

您可以将查询更改为这样:

INSERT INTO upsert_test (id, name, description)
VALUES (COALESCE(:YOUR_ID_PARAM, (select nextval('upsert_test_id_seq'))), 'thing1', 'test')
on conflict (id)
do update set (name , description) = ('thing_updated','test-updated')   
where upsert_test.id = :YOUR_ID_PARAM;

注意我添加了 coalesce函数，因此如果您的参数为 null，则使用序列 nextval。此外，更新现在也使用您的参数。