在INSERT行上更新顺序 [英] Update sequence on row INSERT

问题描述

我有一个在id字段上设置了主键的表，该表使用序列生成数字作为其默认值。插入记录时，此方法工作正常，并且序列已正确更新。但是，如果我插入带有ID字段的记录（例如，从备份导入），则序列不会更新。

I have a table with a primary key set on an id field that uses a sequence to generate numbers as its default value. When inserting records, this works fine and the sequence is correctly updated. However, if I insert records with an id field (e.g. an import from a backup), the sequence is not updated.

CREATE TABLE public.my_table (
  my_id integer NOT NULL DEFAULT NEXTVAL('my_id_seq'),
  name text,
  CONSTRAINT my_primary PRIMARY KEY (myid)
);

CREATE SEQUENCE public.my_id_seq
   START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

这将更新序列：

INSERT INTO my_table (name) VALUES ('Bart');

这不是：

INSERT INTO my_table (my_id, name) VALUES (12, 'Bart');

当序列达到12且我尝试插入时没有ID时会引起问题。

Causing problems when the sequence hits 12 and I try to insert without an ID.

是否有自动执行此操作的良好/标准方法？我知道MySQL在它的 AUTO_INCREMENT 列中做到了（这是我遇到这个问题的方式，我来自MySQL）。我在网上看了一下，发现了一些方法：使用触发器，或在插入后手动更新序列，但是其中许多资源已经很老了。也许9.1版有一些功能可以解决此问题？

Is there a good/standard way to do this automatically? I know MySQL does it in its AUTO_INCREMENT column (which is way I ran into this problem, I came from MySQL). I looked online and found some ways: using a trigger, or manually updating the sequence after inserting, but many of these resources are quite old. Perhaps version 9.1 has some features to address this?

推荐答案

这是一个已知的局限性：在调用 nextval（） 函数，这是您字段的默认值。当您在 INSERT 处向该字段提供数据时，默认值的表达式不求值，这就是为什么不触及该序列值的原因。

That's a known limitation: sequences are incremented during the call of the nextval() function, which is your default value of your field. When you supply data at INSERT to that field, the default value's expression does not evaluates, that's why the sequence's value is not touched.

一种解决方法是在 INSERT 之前/之后设置触发器，以使用 setval（） 。但是通过这种方式，您应该也需要在该字段的 UPDATE 上设置触发器，以修复序列的值，而只是更新一些现有的

A workaround is to set up a trigger before/after INSERT to manually fix the sequence's value with setval(). But this way you should need to set up a trigger on UPDATE on that field too, to fix the sequence's value, when you just update some existing id to a higher id.

另一种解决方法是，编写一个存储函数，该函数可以为该字段&生成可用值。将字段的默认值设置为该函数的返回值。像这样的东西：

Another workaround is, that you write a stored function, which can produce an available value for that field & set your field's default value to that function's return value. Someting, like:

LOOP
    result = nextval('my_id_seq');
    EXIT WHEN NOT EXISTS (SELECT * FROM my_table WHERE my_id = result);
END LOOP;
RETURN result;

但请注意：序列的默认功能对于并发插入是安全的（序列的当前状态）是全球性的-与交易无关）。如果您为这些字段提供了明确的值，则情况并非如此。

But be warned: the default functionality for sequences is safe for concurrent inserts (the current state of the sequence is global - transaction independent). If you supply explicit values to those fields, that won't be the case.

这篇关于在INSERT行上更新顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！