如何重置PostgreSQL表上的ID序列

问题描述

我最近将大量数据从旧数据库导入到一个新的Postgresql数据库中，作为新Django站点中模型的基础。

我使用了旧数据库（作为各表中的行互相引用），但它们并不都是顺序的 - 通常有很大的差距。

我注意到，我通过Django应用程序添加了一个新对象，然后它已经使用了从1开始的ID，这是没有问题的，因为没有非常低的ID的行。

但是一旦达到第一行旧数据，那么postgres显然会抱怨：

 错误：重复键值违反唯一约束django_comments_pkey
 DETAIL：Key（id）=（25）已存在。 
  

查看表格描述我猜想我需要重置每个表上的某种顺序：

 表public.django_comments
列|类型|修饰符
 ----------------- + -------------------------- + -------------------------------------------------- ------------ 
 id |整数| not null default nextval（'django_comments_id_seq':: regclass）
 ... 
  

我需要重新设置这个序列，以便新增的行的ID高于当前的最大ID？

解决方案

运行，它将打印所需的所有重置命令。

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.

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.

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.

But once it reaches the first row of legacy data, then postgres obviously complains:

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?

解决方案

Run sqlsequencereset and it'll print all the reset commands you need.

这篇关于如何重置PostgreSQL表上的ID序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！