SQL 在 postgres 中重置自动增量计数器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5342440/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Reset auto increment counter in postgres
提问by Rad
I would like to force the auto increment field of a table to some value, I tried with this:
我想将表的自动增量字段强制为某个值，我尝试过：
ALTER TABLE product AUTO_INCREMENT = 1453
AND
和
ALTER SEQUENCE product RESTART WITH 1453;
ERROR: relation "your_sequence_name" does not exist
I'm new to postgres :(
我是 postgres 的新手 :(
I have a table productwith Idand namefield
我有一个表product与Id和name领域
回答by araqnid
If you created the table productwith an idcolumn, then the sequence is not simply called product, but rather product_id_seq(that is, ${table}_${column}_seq).
如果您创建了product带有id列的表，则该序列不会被简单地称为product，而是product_id_seq（即${table}_${column}_seq）。
This is the ALTER SEQUENCEcommand you need:
这是ALTER SEQUENCE您需要的命令：
ALTER SEQUENCE product_id_seq RESTART WITH 1453
You can see the sequences in your database using the \dscommand in psql. If you do \d productand look at the default constraint for your column, the nextval(...)call will specify the sequence name too.
您可以使用\dspsql 中的命令查看数据库中的序列。如果你这样做\d product并查看你的列的默认约束，nextval(...)调用也会指定序列名称。
回答by matt snider
Here is the command that you are looking for, assuming your sequence for the product table is product_id_seq:
这是您要查找的命令，假设产品表的序列是 product_id_seq：
ALTER SEQUENCE product_id_seq RESTART WITH 1453;
ALTER SEQUENCE product_id_seq RESTART WITH 1453;
回答by Loolooii
The following command does this automatically for you: This will also delete all the data in the table. So be careful.
以下命令会自动为您执行此操作：这也将删除表中的所有数据。所以要小心。
TRUNCATE TABLE someTable RESTART IDENTITY;
回答by Clodoaldo Neto
To set the sequence counter:
设置序列计数器：
setval('product_id_seq', 1453);
If you don't know the sequence name use the pg_get_serial_sequencefunction:
如果您不知道序列名称，请使用以下pg_get_serial_sequence函数：
select pg_get_serial_sequence('product', 'id');
pg_get_serial_sequence
------------------------
public.product_id_seq
The parameters are the table name and the column name.
参数是表名和列名。
Or just issue a \d productat the psqlprompt:
或者只是\d product在psql提示符下发出一个：
=> \d product
Table "public.product"
Column | Type | Modifiers
--------+---------+------------------------------------------------------
id | integer | not null default nextval('product_id_seq'::regclass)
name | text |
回答by Anwar
Converted from comment for the sake of visitor's convenience
为了访问者的方便，从评论转换而来
It's not clear from this message what the correct syntax is. It is:
从这条消息中不清楚正确的语法是什么。这是：
ALTER SEQUENCE product_id_seq RESTART WITH 1453;
回答by Wiwwil
-- Change the starting value of the sequence
-- 改变序列的起始值
ALTER SEQUENCE project_id_seq RESTART 3000;
Same but dynamic :
相同但动态：
SELECT SETVAL('project_id_seq', (SELECT MAX(id) + 1 FROM project));
I agree the use of a SELECT is disturbing but it works.
我同意 SELECT 的使用令人不安，但它有效。
Source : https://kylewbanks.com/blog/Adding-or-Modifying-a-PostgreSQL-Sequence-Auto-Increment
来源：https: //kylewbanks.com/blog/Adding-or-Modifying-a-PostgreSQL-Sequence-Auto-Increment
回答by Chaudhary
if you want to Resetauto increment from GUI, then follow this steps.
如果要从 GUI重置自动增量，请按照以下步骤操作。
- Go to your Database
- Click on Public
- in the tables Listing page you can see TABSlike 'Tables', 'Views', 'Sequences' like that.
- Click on Sequences
- when you click on 'Sequences' you can see all the Sequences Listing, click on any that you want to Reset
- After that you can see multiple choice like 'Alter', 'Set Value', 'Restart', 'Reset' etc...
- then click on Reset, then add one New Row.
- 转到您的数据库
- 点击公开
- 在表格列表页面中，您可以看到诸如“表格”、“视图”、“序列”之类的TABS。
- 单击序列
- 当您点击“序列”时，您可以看到所有序列列表，点击任何您想要重置的
- 之后，您可以看到多项选择，例如“更改”、“设置值”、“重新启动”、“重置”等...
- 然后点击重置，然后添加一个新行。
回答by BrianB
If you have a table with an IDENTITY column that you want to reset the next value for you can use the following command:
如果您有一个包含 IDENTITY 列的表，您想为其重置下一个值，可以使用以下命令：
ALTER TABLE <table name>
ALTER COLUMN <column name>
RESTART WITH <new value to restart with>;
回答by Vinoth Shankar
To reset the auto increment you have to get your sequence name by using following query.
要重置自动增量，您必须使用以下查询获取序列名称。
Syntax:
句法：
SELECT pg_get_serial_sequence(‘tablename', ‘ columnname‘);
Example:
例子：
SELECT pg_get_serial_sequence('demo', 'autoid');
The query will return the sequence name of autoid as "Demo_autoid_seq"Then use the following query to reset the autoid
查询将返回autoid的序列名称为“Demo_autoid_seq”然后使用以下查询重置autoid
Syntax:
句法：
ALTER SEQUENCE sequenceName RESTART WITH value;
Example:
例子：
ALTER SEQUENCE "Demo_autoid_seq" RESTART WITH 1453;
回答by Raja A
To get sequence id use
获取序列 ID 使用
SELECT pg_get_serial_sequence('tableName', 'ColumnName');
This will gives you sequesce id as tableName_ColumnName_seq
这将为您提供 sequesce id 作为tableName_ColumnName_seq
To Get Last seed number use
获取最后一个种子号使用
select currval(pg_get_serial_sequence('tableName', 'ColumnName'));
or if you know sequence id already use it directly.
或者如果您知道序列 id 已经直接使用它。
select currval(tableName_ColumnName_seq);
It will gives you last seed number
它会给你最后的种子号
To Reset seed number use
重置种子编号使用
ALTER SEQUENCE tableName_ColumnName_seq RESTART WITH 45
