postgresql Postgres 手动更改序列

Postgres manually alter sequence

提问by stef

I'm trying to set a sequence to a specific value.

我正在尝试将序列设置为特定值。

SELECT setval('payments_id_seq'), 21, true

This gives an error:

这给出了一个错误：

ERROR: function setval(unknown) does not exist

ERROR: function setval(unknown) does not exist

Using ALTER SEQUENCEdoesn't seem to work either?

使用ALTER SEQUENCE似乎也不起作用？

ALTER SEQUENCE payments_id_seq LASTVALUE 22

How can this be done?

如何才能做到这一点？

Ref: https://www.postgresql.org/docs/current/static/functions-sequence.html

参考：https: //www.postgresql.org/docs/current/static/functions-sequence.html

回答by NPE

The parentheses are misplaced:

括号错位了：

SELECT setval('payments_id_seq', 21, true);  # next value will be 22

Otherwise you're calling setvalwith a single argument, while it requires two or three.

否则，您将setval使用单个参数进行调用，而它需要两个或三个。

回答by Erwin Brandstetter

This syntax isn't valid in anyversion of PostgreSQL:

此语法在任何版本的 PostgreSQL 中均无效：

ALTER SEQUENCE payments_id_seq LASTVALUE 22

This would work:

这会起作用：

ALTER SEQUENCE payments_id_seq RESTART WITH 22;

and is equivalent to:

相当于：

SELECT setval('payments_id_seq', 22, FALSE);

More in the currentmanual for ALTER SEQUENCEand sequence functions.

更多在当前的手册ALTER SEQUENCE和序列函数中。

Note that setval()expects either (regclass, bigint)or (regclass, bigint, boolean). In the above example I am providing untyped literals. That works too. But if you feed typed variables to the function you may need explicit type caststo satisfy function type resolution. Like:

请注意，setval()希望无论是(regclass, bigint)或(regclass, bigint, boolean)。在上面的例子中，我提供了无类型文字。这也有效。但是，如果您将类型变量提供给函数，您可能需要显式类型转换以满足函数类型解析。喜欢：

SELECT setval(my_text_variable::regclass, my_other_variable::bigint, FALSE);

---

---

For repeated operations you might be interested in:

对于重复操作，您可能感兴趣：

ALTER SEQUENCE payments_id_seq START WITH 22; -- set default
ALTER SEQUENCE payments_id_seq RESTART;       -- without value

START [WITH]stores a default RESTARTnumber, which is used for subsequent RESTARTcalls without value. You need Postgres 8.4 or later for the last part.

START [WITH]存储一个默认RESTART号码，用于后续RESTART无值调用。最后一部分需要 Postgres 8.4 或更高版本。

回答by VaibsVB

Use select setval('payments_id_seq', 21, true);

用 select setval('payments_id_seq', 21, true);

setvalcontains 3 parameters:

setval包含3个参数：

• 1st parameter is sequence_name
• 2nd parameter is Next nextval
• 3rd parameter is optional.

• 第一个参数是 sequence_name
• 第二个参数是 Next nextval
• 第三个参数是可选的。

The use of true or false in 3rd parameter of setval is as follows:

setval 的第 3 个参数中 true 或 false 的使用如下：

SELECT setval('payments_id_seq', 21);           // Next nextval will return 22
SELECT setval('payments_id_seq', 21, true);     // Same as above 
SELECT setval('payments_id_seq', 21, false);    // Next nextval will return 21

The better way to avoid hard-coding of sequence name, next sequence value and to handle empty column table correctly, you can use the below way:

避免序列名称，下一个序列值的硬编码并正确处理空列表的更好方法，您可以使用以下方法：

SELECT setval(pg_get_serial_sequence('table_name', 'id'), coalesce(max(id), 0)+1 , false) FROM table_name;

where table_nameis the name of the table, idis the primary keyof the table

其中table_name是表名，id是primary key表名

回答by Andrzej Bobak

setval('sequence_name', sequence_value)

回答by alanextar

I don't try changing sequence via setval. But using ALTERI was issued how to write sequence name properly. And this only work for me:

我不尝试通过setval. 但是使用ALTER我发出了如何正确编写序列名称。而这只对我有用：

1. Check required sequence name using SELECT * FROM information_schema.sequences;

2. ALTER SEQUENCE public."table_name_Id_seq" restart {number};

   In my case it was ALTER SEQUENCE public."Services_Id_seq" restart 8;

1. 使用检查所需的序列名称 SELECT * FROM information_schema.sequences;

2. ALTER SEQUENCE public."table_name_Id_seq" restart {number};

   就我而言，它是 ALTER SEQUENCE public."Services_Id_seq" restart 8;