Как перенести данные из одной таблицы в другую не нарушив ограничения целостности БД в PostgresSQL?

Question

[JastaFly]

Хочу разбить таблицу на партиции:

CREATE TABLE ticket_part_1 () INHERITS (ticket);
ALTER TABLE ticket_part_1 ADD CONSTRAINT partition_check CHECK (purchase_timestamp < 2022-09-14 21:51:18.872183+03);
INSERT INTO ticket_part_1 (ticket_id, session_id, seat_id, purchase_timestamp)
SELECT *
FROM ticket
WHERE purchase_timestamp < '2022-09-14 21:51:18.872183+03';
DELETE FROM ONLY ticket WHERE purchase_timestamp < '2022-09-14 21:51:18.872183+03';

Но из-за ограничения целостности я не могу удалить данные из родительской таблицы ticket, после их вставки в ticket_part_1. Подскажите как это сделать?!?

Comments

[Akina]

Скопировать данные в новую таблицу.
Изменить внешний ключ, установив ссылку на новую таблицу.
Удалить данные из родительской таблицы.

Answer 1

[JastaFly]

В итоге решил проблему сначала дропнув ограничения целостности и восстановив его в самом конце запроса:

ALTER TABLE order_to_ticket DROP CONSTRAINT ticket_fk;
CREATE TABLE ticket_part_1 () INHERITS (ticket);
ALTER TABLE ticket_part_1 ADD CONSTRAINT partition_check CHECK (purchase_timestamp < '2022-09-14 21:51:18.872183+03');
INSERT INTO ticket_part_1 (ticket_id, session_id, seat_id, purchase_timestamp)
SELECT *
FROM ticket
WHERE purchase_timestamp < '2022-09-14 21:51:18.872183+03';
DELETE FROM ONLY ticket WHERE purchase_timestamp < '2022-09-14 21:51:18.872183+03';
ALTER TABLE ONLY order_to_ticket
ADD CONSTRAINT ticket_fk FOREIGN KEY (ticket_id) REFERENCES ticket(ticket_id) NOT VALID;

Answer 2

[Сергей Томулевич]

Ну у вас скорее наследование с условиями распределения, а не партиционирование, но можно и так.

Ошибка в том, что старую таблицу так же надо делать партицией (в вашем случае потомком)

ALTER TABLE ticket RENAME TO ticket_part_default;
CREATE TABLE ticket (LIKE ticket_part_default);
ALTER TABLE ticket_part_default INHERIT ticket;
...

Далее все как у вас, только удаление делать из ticket_part_default

Comments

[JastaFly]

Сергей Томулевич Не помогло при попытке удаления всё так же срабатывают ограничения целостности:

ERROR: ОШИБКА:  UPDATE или DELETE в таблице "ticket_part_default" нарушает ограничение внешнего ключа "ticket_fk" таблицы "order_to_ticket"
DETAIL:  На ключ (ticket_id)=(7) всё ещё есть ссылки в таблице "order_to_ticket".

Вот запрос который я запустил:

ALTER TABLE ticket RENAME TO ticket_part_default;
CREATE TABLE ticket (LIKE ticket_part_default);
ALTER TABLE ticket_part_default INHERIT ticket;
CREATE TABLE ticket_part_1 () INHERITS (ticket);
ALTER TABLE ticket_part_1 ADD CONSTRAINT partition_check CHECK (purchase_timestamp < '2022-09-14 21:51:18.872183+03');
INSERT INTO ticket_part_1 (ticket_id, session_id, seat_id, purchase_timestamp)
SELECT *
FROM ticket_part_default
WHERE purchase_timestamp < '2022-09-14 21:51:18.872183+03';
DELETE FROM ONLY ticket_part_default WHERE purchase_timestamp < '2022-09-14 21:51:18.872183+03';