将表拆分成多到多个关系:数据迁移
我想知道如何最好地迁移我的数据时，将一个表分成多到多的关系。我已经做了一个简化的例子，我也会张贴一些解决方案，我已经提出了。我正在使用Postgresql数据库。
迁移前的
餐桌人
ID Name Pet PetName
1 Follett Cat Garfield
2 Rowling Hamster Furry
3 Martin Cat Tom
4 Cage Cat Tom迁移后的
餐桌人
ID Name
1 Follett
2 Rowling
3 Martin
4 Cage桌上宠物
ID Pet PetName
6 Cat Garfield
7 Hamster Furry
8 Cat Tom
9 Cat Tom表PersonPet
FK_Person FK_Pet
1 6
2 7
3 8
4 9备注：
- 我将具体复制宠物表中的条目(因为在我的例子中--由于其他相关数据，其中一个可能仍然是客户可编辑的，而另一个则可能无法编辑)。
- 没有唯一标识“宠物”记录的列。
- 对我来说，3-8和4-9是在PersonPet表中链接还是在3-9和4-8中链接并不重要。
- 此外，我省略了处理表的模式更改的所有代码，因为在我的理解中，这与这个问题无关。
我的解决方案
- 创建宠物表时，临时添加包含用于创建此条目的Person表的id的列。
ALTER TABLE Pet ADD COLUMN IdPerson INTEGER;
INSERT INTO Pet (Pet, PetName, IdPerson)
SELECT Pet, PetName, ID
FROM Person;
INSERT INTO PersonPet (FK_Person, FK_Pet)
SELECT ID, IdPerson
FROM Pet;
ALTER TABLE Pet DROP Column IdPerson;- 避免临时修改宠物表
INSERT INTO Pet (Pet, PetName)
SELECT Pet, PetName
FROM Person;
WITH
CTE_Person
AS
(SELECT
Id, Pet, PetName
,ROW_NUMBER() OVER (PARTITION BY Pet, PetName ORDER BY Id) AS row_number
FROM Person
)
,CTE_Pet
AS
(SELECT
Id, Pet, PetName
,ROW_NUMBER() OVER (PARTITION BY Pet, PetName ORDER BY Id) AS row_number
FROM Pet
)
,CTE_Joined
AS
(SELECT
CTE_Person.Id AS Person_Id,
CTE_Pet.Id AS Pet_Id
FROM
CTE_Person
INNER JOIN CTE_Pet ON
CTE_Person.Pet = CTE_Pet.Pet
CTE_Person.PetName = CTE_Pet.PetName
AND CTE_Person.row_number = CTE_Pet.row_number
)
INSERT INTO PersonPet (FK_Person, FK_Pet)
SELECT Person_Id, Pet_Id from CTE_Joined;问题
- 两种解决方案都是正确的吗？(我已经测试了第二个解决方案，结果似乎是正确的，但我可能漏掉了某个角落的情况)
- 这两种解决方案的优缺点是什么？
- 是否有更简单的方法来进行相同的数据迁移？(出于我的好奇心，我也会对稍微修改约束的答案感兴趣(例如，在Pet表中没有重复的条目)，但请指出哪些是:)。
回答 3
Stack Overflow用户
发布于 2015-10-28 23:23:39
实现效果的另一种解决方案(我认为是最简单的解决方案；没有任何CTE-s或附加列)：
create table Pet as
select
Id,
Pet,
PetName
from
Person;
create table PersonPet as
select
Id as FK_Person,
Id as FK_Pet
from
Person;
create sequence PetSeq;
update PersonPet set FK_Pet=nextval('PetSeq'::regclass);
update Pet p set Id=FK_Pet from PersonPet pp where p.Id=pp.FK_Person;
alter table Pet alter column Id set default nextval('PetSeq'::regclass);
alter table Pet add constraint PK_Pet primary key (Id);
alter table PersonPet add constraint FK_Pet foreign key (FK_Pet) references Pet(Id);我们只是使用现有的person id作为宠物的临时id，除非我们使用序列生成一个。
编辑
还可以使用我的方法进行已经完成的模式更改：
insert into Pet(Id, Pet, PetName)
select
Id,
Pet,
PetName
from
Person;
insert into PersonPet(FK_Person, FK_Pet)
select
Id,
Id
from
Person;
select setval('PetSeq'::regclass, (select max(Id) from Person));Stack Overflow用户
发布于 2015-10-28 17:20:52
您可以克服必须向pets表中添加额外列的限制，方法是先插入外键表，然后插入pets表。这允许首先确定映射是什么，然后在第二次传递中填写详细信息。
INSERT INTO PersonPet
SELECT ID, nextval('pet_id_seq'::regclass) as PetID
FROM Person;
INSERT INTO Pet
SELECT FK_Pet, Pet, Petname
FROM Person join PersonPet on (ID=FK_Person);可以使用Vladimir在其答复中概述的通用表表达式机制将其合并为一条语句：
WITH
fkeys AS
(
INSERT INTO PersonPet
SELECT ID, nextval('pet_id_seq'::regclass) as PetID
FROM Person
RETURNING FK_Person as PersonID, FK_Pet as PetID
)
INSERT INTO Pet
SELECT f.PetID, p.Pet, p.Petname
FROM Person p join fkeys f on (p.ID=f.PersonID);就利弊而言：
你的解决方案#1：
- 计算效率更高，它包括两个扫描操作，没有联接和没有排序。
- 空间效率较低，因为它需要在宠物表中存储额外的数据。在Postgres中，DROP列上没有恢复空间(但是可以使用CREATE / DROP来恢复它)。
- 如果您重复执行此操作，可能会导致问题，例如定期添加/删除列，因为您将遇到Postgres max列限制。
我描述的解决方案比解决方案#1计算效率低，因为它需要连接，但比解决方案#2更有效。
Stack Overflow用户
发布于 2015-10-23 00:17:08
是的，你的两种解决方案都是正确的。他们让我想起了这个答案。
几个音符。
第一个变量通过在Pet表中添加额外的列Pet，可以使用RETURNING子句在一个查询中完成。
SQL Fiddle
-- Add temporary PersonID column to Pet
WITH
CTE_Pets
AS
(
INSERT INTO Pet (PersonID, Pet, PetName)
SELECT Person.ID, Person.Pet, Person.PetName
FROM Person
RETURNING ID AS PetID, PersonID
)
INSERT INTO PersonPet (FK_Person, FK_Pet)
SELECT PersonID, PetID
FROM CTE_Pets
;
-- Drop temporary PersonID column不幸的是，Postgres中的RETURNING子句似乎仅限于从目标表返回列，即仅返回实际插入的值。例如，在中，MERGE可以从源表和目标表返回值，从而简化了这类任务，但我在Postgres中找不到类似的内容。
因此，第二个变体没有将显式PersonID列添加到Pet表中，需要将原始的Person与新的Pet连接起来，将旧的PersonID映射到新的PetID。
如果可以像您的示例(Cat Tom)中那样存在重复项，那么使用ROW_NUMBER来分配序列号，以区分重复行，正如您在问题中所显示的那样。
如果没有这样的重复，那么您可以简化映射并去掉ROW_NUMBER。
INSERT INTO Pet (Pet, PetName)
SELECT Pet, PetName
FROM Person;
INSERT INTO PersonPet (FK_Person, FK_Pet)
SELECT
Person.ID AS FK_Person
,Pet.ID AS FK_Pet
FROM
Person
INNER JOIN Pet ON
Person.Pet = Pet.Pet AND
Person.PetName = Pet.PetName
;我看到了第一种方法的一个优点。
如果将PersonID显式地存储在Pet表中，将更容易分几个步骤分批执行这种迁移。第二个变体在PersonPet为空时工作正常，但是如果您已经迁移了一批行，那么筛选所需的行可能会变得很棘手。
https://stackoverflow.com/questions/33166382
复制相似问题
腾讯云开发者
