基于另一列的 PostgreSQL 序列

Question

假设我有一个如下的表格：

Column   |     Type    |                        Notes
---------+------------ +----------------------------------------------------------
 id      | integer     | An ID that's FK to some other table
 seq     | integer     | Each ID gets its own seq number
 data    | text        | Just some text, totally irrelevant.

id + seq 是一个组合键。

我想要看到的是：

ID  | SEQ   |                        DATA
----+------ +----------------------------------------------
 1  | 1     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 1  | 2     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 1  | 3     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 1  | 4     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 2  | 1     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 3  | 1     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 3  | 2     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 3  | 3     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 3  | 4     | Quick brown fox, lorem ipsum, lazy dog, etc etc.

如您所见，id和seq的组合是唯一的。

我不确定如何设置我的表格（或插入语句）来做到这一点。我想插入id和data，导致seq成为依赖于id的子序列。

- Incognito

---

如果seq反映（或应该反映）行插入的顺序，我更愿意使用自动填充的timestamp并在选择行时动态生成一个seq号码。 - user330315

当记录被删除（或更新）时应该发生什么？ - joop

我同意@joop的观点，任何删除操作都可能使得seq在动态生成时变得不可靠。你想用这个结构解决什么问题呢？（例如，如果你的唯一目标是使id, seq成对唯一，那么一个单独的序列就足够了--实际上它会使seq唯一，但这意味着id, seq成对唯一） - pozs

@fthiella，只是好奇，这种“seq”列的实际用途是什么？根据其预期使用方式，可能会有不同的方法。这里一个重要的问题是：序列中是否可以有间隙（由于删除的行或未完成的回滚事务）？如果间隙不可行，则重新计算序列会很昂贵，这意味着在需要时最好动态生成它。如果缺口没关系，那么单个全局序列（标准自增列）就足够了。 - Vladimir Baranov

一个简单的“串行”是正确的选择。 - Erwin Brandstetter

Answer 1

没问题！我们将创建两个表，things和stuff。stuff是您在问题中描述的表格，而things是它所指的表格：

CREATE TABLE things (
    id serial primary key,
    name text
);

CREATE TABLE stuff (
    id integer references things,
    seq integer NOT NULL,
    notes text,
    primary key (id, seq)
);

然后，我们将使用触发器设置things，每当创建一行时就会创建一个新的序列：

CREATE FUNCTION make_thing_seq() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
  execute format('create sequence thing_seq_%s', NEW.id);
  return NEW;
end
$$;

CREATE TRIGGER make_thing_seq AFTER INSERT ON things FOR EACH ROW EXECUTE PROCEDURE make_thing_seq();

现在我们将得到thing_seq_1，thing_seq_2等等...

现在在stuff上再触发另一个操作，以便每次使用正确的序列：

CREATE FUNCTION fill_in_stuff_seq() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
  NEW.seq := nextval('thing_seq_' || NEW.id);
  RETURN NEW;
end
$$;

CREATE TRIGGER fill_in_stuff_seq BEFORE INSERT ON stuff FOR EACH ROW EXECUTE PROCEDURE fill_in_stuff_seq();

这将确保在将行插入stuff时，使用id列查找正确的序列来调用nextval。

下面是一个演示：

test=# insert into things (name) values ('Joe');
INSERT 0 1
test=# insert into things (name) values ('Bob');
INSERT 0 1
test=# select * from things;
 id | name
----+------
  1 | Joe
  2 | Bob
(2 rows)

test=# \d
              List of relations
 Schema |     Name      |   Type   |  Owner
--------+---------------+----------+----------
 public | stuff         | table    | jkominek
 public | thing_seq_1   | sequence | jkominek
 public | thing_seq_2   | sequence | jkominek
 public | things        | table    | jkominek
 public | things_id_seq | sequence | jkominek
(5 rows)

test=# insert into stuff (id, notes) values (1, 'Keychain');
INSERT 0 1
test=# insert into stuff (id, notes) values (1, 'Pet goat');
INSERT 0 1
test=# insert into stuff (id, notes) values (2, 'Family photo');
INSERT 0 1
test=# insert into stuff (id, notes) values (1, 'Redundant lawnmower');
INSERT 0 1
test=# select * from stuff;
 id | seq |        notes
----+-----+---------------------
  1 |   1 | Keychain
  1 |   2 | Pet goat
  2 |   1 | Family photo
  1 |   3 | Redundant lawnmower
(4 rows)

test=#

- Jay Kominek

---

函数 make_thing_seq() 在第二次插入相同的 id 值时会失败，因为您没有检查是否已经存在这样的序列。 - user330315

哦，它正在使用 id 列，这是一个主键，因此唯一。在尝试插入相同的 id 值之前，就会失败，而不是在触发器函数中。 - Jay Kominek

things.id 是主键，但并不能防止我删除和重新插入相同的 id。UPDATE 也没有被覆盖。在同时插入父行和子行的情况下，AFTER 触发器太晚了（子表上的触发器在 BEFORE 时运行）。即使它没有这样做，数据修改 CTE 也会同时虚拟地操作两个表。这有多种失败的方式。即使它能工作，序列也不能保证连续的数字。编号中的间隔是可以预期的。 - Erwin Brandstetter

如果使用create sequence IF NOT EXISTS语句，就可以解决删除和重新插入主键ID可能出现的问题。 - ratijas

我更喜欢 Erwin 的方法，使用 ROW_NUMBER() 和视图，其中 ROW_NUMBER() 生成一个虚拟序列（没有间隙）。为每个唯一组生成一个序列需要 ... 很多额外的数据库对象。 - Martijn Pieters

Answer 2

你可以使用窗口函数来分配你的SEQ值，类似这样：

INSERT INTO YourTable
    (ID, SEQ, DATA)
    SELECT ID, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATA), DATA
        FROM YourSource

- Joe Stefanelli

---

有趣的方法...（寻找这样的东西已经很久了！）使用分区/行号的后果是什么？它是否安全？在什么情况下可能不起作用？ - HLL

窗口函数非常常见，你可能不会遇到“它不能工作的情况”。请查看Joe答案中的链接。一旦开始使用它们，将会在你的SQL语句中打开一个新的可能性世界。 - JNevill

如果你想要删除除最后一条记录以外的任何记录，那么这段代码将会失败。在这种情况下，row_number()函数将与最后一条记录产生冲突。换句话说，在很多常见场景下，这段代码在某个时刻都会失败。 - Andreas Baumgart

Answer 3

如果seq反映（或应反映）插入行的顺序，我更愿意使用自动填充的timestamp，并在选择行时动态生成序列号，使用row_number():

create table some_table
( 
  id          integer   not null,
  inserted_at timestamp not null default current_timestamp,
  data text
);

获取 seq 列的方法如下：

select id,  
       row_number() over (partition by id order by inserted_at) as seq,
       data
from some_table
order by id, seq;

与使用持久化的seq列（尤其是在对id，seq进行索引的情况下）相比，选择操作会稍微慢一些。

如果这成为问题，您可以考虑使用物化视图，或添加seq列，然后定期更新它（出于性能原因，我不建议在触发器中执行此操作）。

SQLFiddle示例：http://sqlfiddle.com/#!15/db69b/1

- user330315

---

也许使用序列比时间戳更好？两行共享相同的时间戳可能会发生吗？这个想法非常简单但很好，但如果删除一条记录，则序列将被重新计算。我不知道OP的想法，但我更喜欢有间隔。 - fthiella

@fthiella：你不能使一个序列依赖于“id”列，你需要为每个可能的“id”值设计一个序列才能实现这一点（本质上是Jay在他的答案中所建议的）。 - user330315

由于current_timestamp是事务的开始时间，因此在您的sqlfiddle示例（以及任何其他单个事务）中插入的所有行都是相同的。那么row_number()只是按照从磁盘读取的行的顺序的函数吗？这在操作/备份和还原过程中保持稳定吗？ - Jay Kominek

你可以在默认值中编写条件的 'nextval'，这需要在脚本中读取公司名称的值。 - elsadek

Answer 4

只是猜测。

INSERT INTO TABLE (ID, SEQ, DATA)
VALUES
(
 IDVALUE,
 (SELECT max(SEQ) +1 FROM TABLE WHERE ID = IDVALUU),
 DATAVALUE
);

- Abercrombieande

---

这基本上就是Joe的答案，只是不够高效。 - user330315

需要表的独占锁才能正确工作吗？如果两个像这样的插入同时运行会怎么样？ - Radek Postołowicz

我认为这些 max+1 的解决方案可能不可靠。为了在Postgresql上进行测试，我创建了 tbl 并插入了一行：id=1。然后我打开了两个连接并在每个连接上启动了一个事务。我执行了 INSERT INTO tbl SELECT MAX(id)+1 FROM tbl。第一个插入完成后，第二个等待第一个，正如预期的那样。我提交了第一个事务，第二个立即输出：ERROR: duplicate key value violates unique constraint "tbl_pkey" DETAIL: Key (id)=(2) already exists. 我提交了第二个事务，它自动回滚了。 - Clint Pachl

Answer 5

这里有一个使用标准SQL的简单方法：

INSERT INTO mytable (id, seq, data)
SELECT << your desired ID >>,
       COUNT(*) + 1,
       'Quick brown fox, lorem ipsum, lazy dog, etc etc.'
FROM mytable
WHERE id = << your desired ID (same as above) >>;

请查看SQL Fiddle演示。

（如果您想要更聪明一些，可以考虑创建一个触发器，在插入后立即使用相同的方法更新行。）

- Steve Chambers

Answer 6

我有同样的需求，需要动态存储类似树形结构的数据，而不是一次性添加所有ID。
我不想为每个组使用序列表，因为可能会有成千上万个组。
它在一个密集的多处理环境中运行，所以必须具备竞争条件保护能力。
这里是第一级插入函数。其他级别遵循相同的原则。

每个组都有独立的不可重用的连续ID，该函数接收组名和子组名，返回现有的ID或创建新的ID并返回。
我尝试了一个循环来进行单个选择，但代码太长且难以阅读。

CREATE OR REPLACE FUNCTION getOrInsert(myGroupName TEXT, mySubGroupName TEXT)
  RETURNS INT AS
$BODY$
DECLARE
   myId INT;
BEGIN -- 1st try to get it if it already exists
   SELECT id INTO myId FROM myTable
      WHERE groupName=myGroupName AND subGroupName=mySubGroupName;
   IF NOT FOUND THEN
      -- Only 1 session can get it but others can read
      LOCK TABLE myTable IN SHARE ROW EXCLUSIVE MODE; 
      -- 2nd try in case of race condition
      SELECT id INTO myId FROM myTable
         WHERE groupName=myGroupName AND subGroupName=mySubGroupName;
      IF NOT FOUND THEN -- Doesn't exist. Get next ID for this group.
         SELECT COALESCE(MAX(id), 0)+1 INTO myId FROM myTable
            WHERE groupName=myGroupName;
         INSERT INTO myTable (groupName, id, subGroupName)
            VALUES (myGroupName, myId, mySubGroupName);
      END IF;
   END IF;
   RETURN myId;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE COST 100;

尝试一下：

CREATE TABLE myTable (GroupName TEXT, SubGroupName TEXT, id INT);
SELECT getOrInsert('groupA', 'subgroupX'); -- Returns 1
...
SELECT * FROM myTable;
 groupname | subgroupname | id 
-----------+--------------+----
 groupA    | subgroupX    |  1
 groupA    | subgroupY    |  2
 groupA    | subgroupZ    |  3
 groupB    | subgroupY    |  1

- Le Droid

Answer 7

PostgreSQL支持分组唯一列，如下：

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

请参阅PostgreSQL文档 - 第5.3.3节

很简单 :-)

- Ryan Kinal

---

独特的部分不是我的主要关注点，而是以子序列的方式将数据输入。 - Incognito

Answer 8

我没有postgresql特定的经验，但你能在插入语句中使用子查询吗？类似于Mysqlish中的：

INSERT INTO MYTABLE SET 
   ID=4, 
   SEQ=(  SELECT MAX(SEQ)+1 FROM MYTABLE WHERE ID=4  ),
   DATA="Quick brown fox, lorem ipsum, lazy dog, etc etc."

- Nathan

---

该语法是无效的 SQL。insert 没有 SET。 - user330315

实际上，这个语法是针对MySQL的，但主要思想在这里。 - Yannoff