在PostgreSQL中导入数据后，重复的键值违反了唯一约束 [英] After importing data in PostgreSQL, duplicate key value violates unique constraint

问题描述

我最近将Rails应用程序迁移到了PostgreSQL，以便利用全文本搜索。

I recently migrated my rails app to PostgreSQL in order to take advantage of fulltext search.

由于迁移恰逢迁移到新的Web主机，因此迁移步骤是：

Since the migration coincided with moving to a new webhost, the steps for migration were:

1. 使用适当的database.yml文件在新服务器上部署应用程序和db：create / db：schema：load


2. 仅从现有MySQL生产数据库中转储数据


3. 将数据导入PostgreSQL数据库

应用程序已成功运行，但是尝试向数据库中添加新内容时出现了问题。例如，当我运行rake任务更新我的Twitter feed时：

The application is running successfully but the issue comes when trying to add new content to the database. For example, when I run the rake task to update my twitter feed:

PG::Error: ERROR:  duplicate key value violates unique constraint "twitter_feeds_pkey" DETAIL:  Key (id)=(3) already exists.

所有其他模型，创建新文章，用户等也都会发生这种情况。在开发中，我可以看到将插入语句发布n + 1次将成功保存记录而不会出错。

This also happens for all other models, creating new articles, users etc. In development I can see that posting the insert statement n+1 times will successfully save the record without error.

我的问题是：如何告诉PostgreSQL从现有数据？

我已经阅读了 REINDEX 页面，但认为这不是真的吗？我正在寻找的手术。

I've read the REINDEX page but don't think that is really the operation I'm looking for.

推荐答案

如果架构包含序列或序列列，则应将其重置为相应列中的最大值。 （通常，您不应该从文件中导入序列，而应给予它们自动递增的自由。）

If the schema contains serial or sequence columns, you should reset these to the max value that occurs in the corresponding column. (normally you should not import the serials from a file, but give them the freedom to autoincrement.)

对于所有导入的表，您应该标识序列字段并运行以下命令他们的代码。 （将您的架构名称替换为 sch，将表名称替换为 mytable，并将您的id列名称替换为 id）

For all imported tables you should identify the sequence fields and run the following code on them. (substitute your schema name for "sch", your table name for "mytable" and your id column name for "id")

WITH mx AS ( SELECT MAX(id) AS id FROM sch.mytable)
SELECT setval('sch.mytable_id_seq', mx.id) AS curseq
FROM mx
        ;

这篇关于在PostgreSQL中导入数据后，重复的键值违反了唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！