如何在PostgreSQL中进行UPDATE或INSERT操作

Question

我想要在PostgreSQL中更新或插入一列，而不是使用“INSERT ... ON CONFLICT ...”进行插入或更新，因为会有更多的更新而不是插入操作。另外，我还有一个自动递增的id列，该列是使用SERIAL定义的，所以每次尝试进行插入或更新时都会增加id列，这不是我想要的结果，我希望只有在进行插入时id列才会增加，以便所有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;

谢谢 :)

- Awesome Stickz

---

表格中自动生成/序列化的ID在所有情况下不应该期望是连续的且没有间隙。但是为什么不提供您已经使用的语句呢？除了一般性描述之外，您没有给我们任何真正帮助您的东西。最终，您可能需要利用一些PL/pgSQL（SQL过程化语言）来实现您的目标。 - Marc

我不明白insert ... on conflict...的问题究竟是什么。 - user330315

已添加了查询，您现在可以查看。insert ... on conflict ...的问题在于这里的ID存在间隙，我认为尝试多次插入并不是一个好主意，因为在大多数情况下，只需要更新即可。 - Awesome Stickz

序列生成值中的间隙并不需要担心。生成的主键值的唯一工作就是保持唯一性。无论该值是1、2、-34还是48756485都没有关系。 - user330315

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间隙将是不可避免的。如果您担心值可能变得太大，请改用bigserial，让PostgreSQL完成其工作。

相关主题：serial in postgres is being increased even though I added on conflict do nothing

- Jim Jones

---

我不确定你是直接插入ID还是用DEFAULT来做的，但在我的情况下，使用DEFAULT跳过了很多ID。另外，我已经在问题中添加了我使用的查询，请查看并告诉我是否有做错什么。 - Awesome Stickz

@AwesomeStickz，我刚刚在你的留言中添加了一条评论。 - Jim Jones

好的，谢谢你告诉我这个信息。我只是担心它可能会达到bigserial的最大值，但实际上这非常非常难以做到，所以这不再是一个问题了。 - Awesome Stickz