sql - 如何在 PostgreSQL 中更新或插入

Question

我要UPDATE or INSERT PostgreSQL中的一列而不是 做INSERT or UPDATE使用 INSERT ... ON CONFLICT ...因为会有更多的更新而不是更多的插入，而且我有一个使用 SERIAL 定义的自动递增 id 列所以它每次尝试插入或更新时都会增加 id 列，这不是我想要的，我希望 id 列只有在它是 INSERT 时才会增加，以便所有 id 都按顺序排列
该表是这样创建的

CREATE TABLE IF NOT EXISTS table_name (
    id SERIAL PRIMARY KEY,

    user_id varchar(30) NOT NULL,
    item_name varchar(50) NOT NULL,
    code_uses bigint NOT NULL,

    UNIQUE(user_id, item_name)
)

我使用的查询是

INSERT INTO table_name
VALUES (DEFAULT, 'some_random_id', 'some_random_name', 1)
ON CONFLICT (user_id, item_name)
DO UPDATE SET code_uses = table_name.code_uses + 1;

谢谢 :)

Answer 1

Upserts 在 PostgreSQL 中完全按照您的描述进行操作。
考虑这个表和记录

CREATE TABLE t (id SERIAL PRIMARY KEY, txt TEXT);
INSERT INTO t (txt) VALUES ('foo'),('bar');

SELECT * FROM t ORDER BY id;

 id | txt 
----+-----
  1 | foo
  2 | bar
(2 Zeilen)

使用 upserts id 只会在插入新记录时增加

INSERT INTO t VALUES (1,'foo updated'),(3,'new record')
ON CONFLICT (id) DO UPDATE SET txt = EXCLUDED.txt;

SELECT * FROM t ORDER BY id;

 id |     txt     
----+-------------
  1 | foo updated
  2 | bar
  3 | new record
(3 Zeilen)

编辑 (见评论):这是 serial 的预期行为列，因为它们只不过是一种奇特的使用方式 sequences .长话短说:使用 upserts the gaps will be inevitable .如果您担心该值可能会变得太大，请使用 bigserial而是让 PostgreSQL 完成它的工作。
相关主题: serial in postgres is being increased even though I added on conflict do nothing