更新 Postgres 中作为主键的部分(订单号) [英] Update part (order number) that is primary key in Postgres

问题描述

我有桌子:

book_id | part | name
1       | 1    | chap 1
1       | 2    | chap 2
1       | 3    | chap 3
1       | 4    | chap 4

主键是 book_id 和 part.

Primary key is book_id and part.

如何删除第 2 部分并更新部分顺序以获取:

How can delete part 2 and update order of parts to get:

book_id | part | name
1       | 1    | chap 1
1       | 2    | chap 3
1       | 3    | chap 4

我可以做一个事务并首先删除第 2 部分，但是我怎样才能在不出现重复主键错误的情况下更新部分列?

I can do a transaction and firstly delete part 2, but how can i then update part column without getting duplicate primary key error?

推荐答案

我会选择不同的方法.不保留零件编号，而是保留零件的顺序:

I would choose a different approach. Instead of persisting the part number, persist the order of the parts:

CREATE TABLE book_part (
   book_id bigint NOT NULL,
   part_order real NOT NULL,
   name text NOT NULL,
   PRIMARY KEY (book_id, part_order)
);

输入的第一部分的 part_order 为 0.0.如果您在开头或结尾添加一个部分，您只需为 part_order 分配比之前的最小值或最大值少或多 1.0 的值.如果在两个现有部分之间插入一个部分，则指定一个 part_order，即相邻部分的算术平均值.

The first part that gets entered gets a part_order of 0.0. If you add a part at the beginning or the end, you just assign to part_order 1.0 less or more than the previous minimum or maximum. If you insert a part between two existing parts, you assign a part_order that is the arithmetic mean of the adjacent parts.

示例:

-- insert the first part

INSERT INTO book_part VALUES (1, 0.0, 'Introduction');

-- insert a part at the end

INSERT INTO book_part VALUES (1, 1.0, 'Getting started with PostgreSQL');

-- insert a part between the two existing parts

INSERT INTO book_part VALUES (1, 0.5, 'The history of PostgreSQL');

-- adding yet another part between two existing parts

INSERT INTO book_part VALUES (1, 0.25, 'An introductory example');

查询表时计算实际零件号:

The actual part number is calculated when you query the table:

SELECT book_id,
       row_number() OVER (PARTITION BY book_id ORDER BY part_order) AS part,
       name
FROM book_part;

这样做的好处在于，您在添加或删除部件时无需更新很多行.

The beauty of that is that you don't need to update a lot of rows when you add or delete a part.

这篇关于更新 Postgres 中作为主键的部分(订单号)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！