如何重置 PostgreSQL 表上 ID 的序列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14589634/
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
How to reset the sequence for IDs on PostgreSQL tables
提问by Phil Gyford
I recently imported a lot of data from an old database into a new Postgresql database as the basis for models in a new Django site.
我最近将旧数据库中的大量数据导入到新的 Postgresql 数据库中，作为新 Django 站点中模型的基础。
I used the IDs from the old database (as rows in various tables refer to each other), but they aren't all sequential - there are often large gaps.
我使用了旧数据库中的 ID（因为各个表中的行相互引用），但它们并不都是连续的 - 通常存在很大的差距。
I've noticed that when I add a new object via the Django app, then it has been using IDs starting from 1, which hasn't been a problem so far as there were no rows with very low IDs.
我注意到当我通过 Django 应用程序添加一个新对象时，它一直使用从 1 开始的 ID，这不是问题，因为没有 ID 非常低的行。
But once it reaches the first row of legacy data, then postgres obviously complains:
但是一旦到达遗留数据的第一行，那么 postgres 显然会抱怨：
ERROR: duplicate key value violates unique constraint "django_comments_pkey"
DETAIL: Key (id)=(25) already exists.
Looking at the table descriptions I'm guessing I need to reset some kind of sequence on each table:
查看表格描述，我猜我需要在每个表格上重置某种序列：
Table "public.django_comments"
Column | Type | Modifiers
-----------------+--------------------------+--------------------------------------------------------------
id | integer | not null default nextval('django_comments_id_seq'::regclass)
...
What do I need to do to reset that sequence, so that new rows are added with IDs higher than the current maximum ID?
我需要做什么来重置该序列，以便添加 ID 高于当前最大 ID 的新行？
回答by Dmitry Shevchenko
Run sqlsequenceresetand it'll print all the reset commands you need.
运行sqlsequencereset它将打印您需要的所有重置命令。
回答by Paolo Melchiorre
As suggested by "Dmitry Shevchenko" you can run sqlsequenceresetto solve your problem.
正如“Dmitry Shevchenko”所建议的那样，您可以运行sqlsequencereset来解决您的问题。
or
或者
You can execute the SQL query generated by sqlsequenceresetfrom within python in this way (using the default database):
您可以通过sqlsequencereset这种方式（使用默认数据库）执行从 Python 内部生成的 SQL 查询：
from django.core.management.color import no_style
from django.db import connection
from myapps.models import MyModel1, MyModel2
sequence_sql = connection.ops.sequence_reset_sql(no_style(), [MyModel1, MyModel2])
with connection.cursor() as cursor:
for sql in sequence_sql:
cursor.execute(sql)
I tested this code with Python3.6, Django 2.0and PostgreSQL 10.
我用Python3.6、Django 2.0和PostgreSQL 10测试了这段代码。
回答by Danielle Madeley
Here's a short snippet to reset all sequences in Django 1.9+ (based on http://djangosnippets.org/snippets/2774/) and compatible with Python 3:
这是一个简短的片段，用于重置 Django 1.9+ 中的所有序列（基于http://djangosnippets.org/snippets/2774/）并与 Python 3 兼容：
import os
from io import StringIO
os.environ['DJANGO_COLORS'] = 'nocolor'
from django.core.management import call_command
from django.apps import apps
from django.db import connection
commands = StringIO()
cursor = connection.cursor()
for app in apps.get_app_configs():
label = app.label
call_command('sqlsequencereset', label, stdout=commands)
cursor.execute(commands.getvalue())
回答by Majid Zandi
PostgreSQL Command:
ALTER SEQUENCE app_model_id_seq RESTART WITH 1
PostgreSQL 命令：
ALTER SEQUENCE app_model_id_seq RESTART WITH 1
回答by Clodoaldo Neto
select setval('django_comments_id_seq', 12345);
回答by Marco Frattallone
This snippet Run sqlsequencereset on all appsreset all IDs of all Empty Models
此代码段在所有应用程序上运行 sqlsequencereset重置所有空模型的所有 ID
回答by rowman
based on @Paolo Melchiorre I created a custom management command, which populates all the models from chosen apps.
基于@Paolo Melchiorre，我创建了一个自定义管理命令，它填充所选应用程序中的所有模型。
from django.core.management.base import BaseCommand
from django.apps import apps
from django.core.management.color import no_style
from django.db import connection
class Command(BaseCommand):
def handle(self, *args, **kwargs):
self.stdout.write('Reset AutoFields ...')
APPS = ['app1', 'app2']
APPS = [apps.get_app_config(app) for app in APPS]
models = []
for app in APPS:
models.extend(list(app.get_models()))
sequence_sql = connection.ops.sequence_reset_sql(no_style(), models)
with connection.cursor() as cursor:
for sql in sequence_sql:
self.stdout.write(sql)
cursor.execute(sql)
self.stdout.write(self.style.SUCCESS('Reset AutoField complete.'))
tested using python 3.7and django 2.2.
使用python 3.7和测试django 2.2。
