在通过SQL文件或者Navicat工具将MySQL数据库同步到PostgreSQL中时出现[ERR] 251＞ ERROR: relation “no“ already exists

问题描述

今天在使用sql文件同步MySQL数据库到PostgreSQL数据库某模式下时，出现了如下错误：

CREATE UNIQUE INDEX "no" ON "public"."erp_purchase_order" USING btree (
  "no" ASC
)

起初我以为时sql文件导出有问题，然后我适用Navicat 16工具中的数据传输工具对数据进行传输，传输数据没有问题，但是还是出现了这个问题：

[ERR] 251> CREATE UNIQUE INDEX "no" ON "public"."erp_purchase_order" USING btree (
  "no" ASC
)
[ERR] 251> ERROR:  relation "no" already exists

问题排查

在执行日志中可以清楚的看到ERROR: relation "no" already exists报错原因就是这个no的索引关系已经存在了；那为什么MySQL数据库中不会报错，而PostgreSQL中就有问题了，我大概了解了一下。

MySQL

在MySQL数据库中存在主索引和二级索引 ，主索引通常被称为聚簇索引，主索引就是表本身；

• 注意，对于行存储，这是正确的。数据库可能使用不同的存储模型，如列存储、图形或文档存储，从根本上讲，这些也可以作为潜在的值。

如果在主索引中查找一个键，你会找到包含该键的页面和它的值，该值是该键对应的完整行，不需要额外的 I/O 操作来获取其他列。
在二级索引中，键是你索引的列（或多个列），而值是指向实际存储完整行位置的指针。二级索引叶子页面的值通常是主键。
这就是 MySQL 的情况。在 MySQL 中，所有的表都必须有一个主索引，而所有额外的二级索引都指向主键。如果你在 MySQL 表中不创建主键，系统会为你自动创建一个。

Postgres

在 Postgres 中，严格来说没有主索引，所有的索引都是二级索引，它们都指向加载在堆中的数据页中由系统管理的元组标识符（tuple ids）。堆中的表数据是无序的，不像主索引叶子页是有序的。因此，如果你插入了 1-100 行，并且它们都在同一页中，然后后来更新了 1-20 行，这 20 行可能会跳转到另一页，并且变得无序。而在聚簇主索引中，插入操作必须按照键的顺序插入到相应的页中。这就是为什么 Postgres 表通常被称为 “堆有序表"而不是"索引组织表”。

需要注意的是，在 Postgres 中，更新和删除实际上是插入操作。每次更新或删除都会创建一个新的元组标识符（tuple id），而旧的元组标识符则保留为了多版本并发控制（MVCC）的原因。我稍后会在本文中探讨这个问题。

事实上，仅仅使用元组标识符是不够的。实际上，我们需要同时知道元组标识符和页面编号，这被称为 c_tid。想一想，仅仅知道元组标识符是不够的，我们需要知道元组所在的页。这是在 MySQL 中不需要做的事情，因为我们实际上是通过查找来找到主键所在的页。而在 Postgres 中，我们只需要进行一次 I/O 操作就可以获取到完整的行数据。

结论

由于PostgreSQL数据库中所有索引都是二级索引,整个模式下所有索引全部存储在一起，索引名称不能重复 所以更改索引名称就行了。

--
CREATE UNIQUE INDEX "no" ON "public"."erp_purchase_order" USING btree (
  "no" ASC
);
--- 上述创建索引失败，将上面索引进行如下修改
CREATE UNIQUE INDEX "idx_no_epo" ON "public"."erp_purchase_order" USING btree (
  "no" ASC
);
-- 修改索引名称不重复即可