Разделить таблицу во многих отношениях: миграция данных

Ответ 1

Другое решение для достижения эффекта, которое вы описали (на мой взгляд, самый простой, без каких-либо CTE-команд или дополнительных столбцов):

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);

Мы просто используем существующий идентификатор лица в качестве временного идентификатора для домашних животных, если только мы не сгенерируем одну используемую последовательность.

Edit

Также возможно использовать мой подход с уже внесенными изменениями схемы:

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));

Ответ 2

Да, ваши оба решения верны. Они напоминают мне этот ответ.

Несколько заметок.

Первый вариант с добавлением дополнительного столбца PersonID в таблицу 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

К сожалению, кажется, что предложение RETURNING в INSERT в Postgres ограничено возвратом столбцов только из таблицы назначения, то есть только те значения, которые были фактически вставлены. Например, в MS SQL Server 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 пуст, но если вы уже перенесли партию строк, может оказаться сложным фильтровать нужные строки.

Ответ 3

Вы можете преодолеть ограничение необходимости добавить дополнительный столбец на стол для домашних животных, вставив сначала в таблицу внешнего ключа, а затем в таблицу домашних животных. Это позволяет определить, что такое отображение, а затем заполнить детали во втором проходе.

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);

Это можно объединить в одно утверждение, используя общие механизмы выражения таблицы, изложенные Владимиром в его ответе:

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 TABLE AS/DROP TABLE).
• Может возникнуть проблема, если вы делаете это повторно, например. добавление/удаление столбца регулярно, потому что вы столкнетесь с максимальным пределом столбца Postgres.

Решение, которое я изложил, менее эффективно, чем ваше решение # 1, менее эффективно, потому что для этого требуется соединение, но оно более эффективно, чем решение # 2.