clear.sql

COPY (SELECT *
      FROM car_recommendation_part
      WHERE recommendation_id IN (
          SELECT id
          from car_recommendation
          WHERE car_id NOT IN (
              SELECT car.id
              FROM orders
                       JOIN car ON car.uuid = orders.car_id
              WHERE car_id IS NOT NULL
              UNION ALL
              SELECT car.id
              FROM calendar_entry_order_info
                       JOIN car ON car.uuid = calendar_entry_order_info.car_id
              WHERE car_id IS NOT NULL
          )
      )) TO '/var/lib/postgresql/data/car_recommendation_part.clear_backup.sql';
DELETE
FROM car_recommendation_part
WHERE recommendation_id IN (
    SELECT id
    from car_recommendation
    WHERE car_id NOT IN (
        SELECT car.id
        FROM orders
                 JOIN car ON car.uuid = orders.car_id
        WHERE car_id IS NOT NULL
        UNION ALL
        SELECT car.id
        FROM calendar_entry_order_info
                 JOIN car ON car.uuid = calendar_entry_order_info.car_id
        WHERE car_id IS NOT NULL
    )
);

COPY (SELECT *
      FROM car_recommendation
      WHERE car_id NOT IN (
          SELECT car.id
          FROM orders
                   JOIN car ON car.uuid = orders.car_id
          WHERE car_id IS NOT NULL
          UNION ALL
          SELECT car.id
          FROM calendar_entry_order_info
                   JOIN car ON car.uuid = calendar_entry_order_info.car_id
          WHERE car_id IS NOT NULL
      )) TO '/var/lib/postgresql/data/car_recommendation.clear_backup.sql';
DELETE
FROM car_recommendation
WHERE car_id NOT IN (
    SELECT car.id
    FROM orders
             JOIN car ON car.uuid = orders.car_id
    WHERE car_id IS NOT NULL
    UNION ALL
    SELECT car.id
    FROM calendar_entry_order_info
             JOIN car ON car.uuid = calendar_entry_order_info.car_id
    WHERE car_id IS NOT NULL
);

COPY (SELECT *
      FROM car_note
      WHERE car_id NOT IN (
          SELECT car.id
          FROM orders
                   JOIN car ON car.uuid = orders.car_id
          WHERE car_id IS NOT NULL
          UNION ALL
          SELECT car.id
          FROM calendar_entry_order_info
                   JOIN car ON car.uuid = calendar_entry_order_info.car_id
          WHERE car_id IS NOT NULL
      )) TO '/var/lib/postgresql/data/car_note.clear_backup.sql';
DELETE
FROM car_note
WHERE car_id NOT IN (
    SELECT car.id
    FROM orders
             JOIN car ON car.uuid = orders.car_id
    WHERE car_id IS NOT NULL
    UNION ALL
    SELECT car.id
    FROM calendar_entry_order_info
             JOIN car ON car.uuid = calendar_entry_order_info.car_id
    WHERE car_id IS NOT NULL
);

COPY (SELECT *
      FROM car
      WHERE uuid NOT IN (
          SELECT car_id
          FROM orders
          WHERE car_id IS NOT NULL
          UNION ALL
          SELECT car_id
          FROM calendar_entry_order_info
          WHERE car_id IS NOT NULL
      )) TO '/var/lib/postgresql/data/car.clear_backup.sql';
DELETE
FROM car
WHERE uuid NOT IN (
    SELECT car_id
    FROM orders
    WHERE car_id IS NOT NULL
    UNION ALL
    SELECT car_id
    FROM calendar_entry_order_info
    WHERE car_id IS NOT NULL
);

COPY (SELECT *
      FROM operand_note
      WHERE operand_id NOT IN (
          SELECT o.id
          FROM orders
                   JOIN operand o on orders.uuid = o.uuid
          UNION ALL
          SELECT person_id
          FROM employee
          UNION ALL
          SELECT o.id
          FROM calendar_entry_order_info
                   JOIN operand o on calendar_entry_order_info.customer_id = o.uuid
          UNION ALL
          SELECT o.id
          FROM income
                   JOIN operand o on income.supplier_id = o.uuid
      )) TO '/var/lib/postgresql/data/operand_note.clear_backup.sql';
DELETE
FROM operand_note
WHERE operand_id NOT IN (
    SELECT o.id
    FROM orders
             JOIN operand o on orders.uuid = o.uuid
    UNION ALL
    SELECT person_id
    FROM employee
    UNION ALL
    SELECT o.id
    FROM calendar_entry_order_info
             JOIN operand o on calendar_entry_order_info.customer_id = o.uuid
    UNION ALL
    SELECT o.id
    FROM income
             JOIN operand o on income.supplier_id = o.uuid
);

COPY (SELECT *
      FROM organization
      WHERE id NOT IN (
          SELECT o.id
          FROM orders
                   JOIN operand o on orders.customer_id = o.uuid
          UNION ALL
          SELECT person_id
          FROM employee
          UNION ALL
          SELECT o.id
          FROM calendar_entry_order_info
                   JOIN operand o on calendar_entry_order_info.customer_id = o.uuid
          UNION ALL
          SELECT o.id
          FROM income
                   JOIN operand o on income.supplier_id = o.uuid
      )) TO '/var/lib/postgresql/data/organization.clear_backup.sql';
DELETE
FROM organization
WHERE id NOT IN (
    SELECT o.id
    FROM orders
             JOIN operand o on orders.customer_id = o.uuid
    UNION ALL
    SELECT person_id
    FROM employee
    UNION ALL
    SELECT o.id
    FROM calendar_entry_order_info
             JOIN operand o on calendar_entry_order_info.customer_id = o.uuid
    UNION ALL
    SELECT o.id
    FROM income
             JOIN operand o on income.supplier_id = o.uuid
);

COPY (SELECT *
      FROM person
      WHERE id NOT IN (
          SELECT o.id
          FROM orders
                   JOIN operand o on orders.customer_id = o.uuid
          UNION ALL
          SELECT person_id
          FROM employee
          UNION ALL
          SELECT o.id
          FROM calendar_entry_order_info
                   JOIN operand o on calendar_entry_order_info.customer_id = o.uuid
          UNION ALL
          SELECT o.id
          FROM income
                   JOIN operand o on income.supplier_id = o.uuid
      )) TO '/var/lib/postgresql/data/person.clear_backup.sql';
DELETE
FROM person
WHERE id NOT IN (
    SELECT o.id
    FROM orders
             JOIN operand o on orders.customer_id = o.uuid
    UNION ALL
    SELECT person_id
    FROM employee
    UNION ALL
    SELECT o.id
    FROM calendar_entry_order_info
             JOIN operand o on calendar_entry_order_info.customer_id = o.uuid
    UNION ALL
    SELECT o.id
    FROM income
             JOIN operand o on income.supplier_id = o.uuid
);

COPY (SELECT *
      FROM operand
      WHERE id NOT IN (
          SELECT o.id
          FROM orders
                   JOIN operand o on orders.customer_id = o.uuid
          UNION ALL
          SELECT person_id
          FROM employee
          UNION ALL
          SELECT o.id
          FROM calendar_entry_order_info
                   JOIN operand o on calendar_entry_order_info.customer_id = o.uuid
          UNION ALL
          SELECT o.id
          FROM income
                   JOIN operand o on income.supplier_id = o.uuid
      )) TO '/var/lib/postgresql/data/operand.clear_backup.sql';
DELETE
FROM operand
WHERE id NOT IN (
    SELECT o.id
    FROM orders
             JOIN operand o on orders.customer_id = o.uuid
    UNION ALL
    SELECT person_id
    FROM employee
    UNION ALL
    SELECT o.id
    FROM calendar_entry_order_info
             JOIN operand o on calendar_entry_order_info.customer_id = o.uuid
    UNION ALL
    SELECT o.id
    FROM income
             JOIN operand o on income.supplier_id = o.uuid
);

clearfix.sql

COPY operand FROM '/var/lib/postgresql/data/operand.clear_backup.sql';
COPY person FROM '/var/lib/postgresql/data/person.clear_backup.sql';
COPY organization FROM '/var/lib/postgresql/data/organization.clear_backup.sql';

COPY (SELECT *
      FROM organization
      WHERE id NOT IN (
          SELECT o.id
          FROM orders
                   JOIN operand o on orders.customer_id = o.uuid
          UNION ALL
          SELECT person_id
          FROM employee
          UNION ALL
          SELECT o.id
          FROM calendar_entry_order_info
                   JOIN operand o on calendar_entry_order_info.customer_id = o.uuid
          UNION ALL
          SELECT o.id
          FROM income
                   JOIN operand o on income.supplier_id = o.uuid
      )) TO '/var/lib/postgresql/data/organization.sql';
DELETE
FROM organization
WHERE id NOT IN (
    SELECT o.id
    FROM orders
             JOIN operand o on orders.customer_id = o.uuid
    UNION ALL
    SELECT person_id
    FROM employee
    UNION ALL
    SELECT o.id
    FROM calendar_entry_order_info
             JOIN operand o on calendar_entry_order_info.customer_id = o.uuid
    UNION ALL
    SELECT o.id
    FROM income
             JOIN operand o on income.supplier_id = o.uuid
    UNION ALL
    SELECT o.id
    FROM order_item_service
             JOIN operand o on order_item_service.worker_id = o.uuid
    UNION ALL
    SELECT supplier_id
    FROM order_item_part
    WHERE supplier_id IS NOT NULL
);

COPY (SELECT *
      FROM person
      WHERE id NOT IN (
          SELECT o.id
          FROM orders
                   JOIN operand o on orders.customer_id = o.uuid
          UNION ALL
          SELECT person_id
          FROM employee
          UNION ALL
          SELECT o.id
          FROM calendar_entry_order_info
                   JOIN operand o on calendar_entry_order_info.customer_id = o.uuid
          UNION ALL
          SELECT o.id
          FROM income
                   JOIN operand o on income.supplier_id = o.uuid
          UNION ALL
          SELECT o.id
          FROM order_item_service
                   JOIN operand o on order_item_service.worker_id = o.uuid
          UNION ALL
          SELECT supplier_id
          FROM order_item_part
          WHERE supplier_id IS NOT NULL
      )) TO '/var/lib/postgresql/data/person.sql';
DELETE
FROM person
WHERE id NOT IN (
    SELECT o.id
    FROM orders
             JOIN operand o on orders.customer_id = o.uuid
    UNION ALL
    SELECT person_id
    FROM employee
    UNION ALL
    SELECT o.id
    FROM calendar_entry_order_info
             JOIN operand o on calendar_entry_order_info.customer_id = o.uuid
    UNION ALL
    SELECT o.id
    FROM income
             JOIN operand o on income.supplier_id = o.uuid
    UNION ALL
    SELECT o.id
    FROM order_item_service
             JOIN operand o on order_item_service.worker_id = o.uuid
    UNION ALL
    SELECT supplier_id
    FROM order_item_part
    WHERE supplier_id IS NOT NULL
);

COPY (SELECT *
      FROM operand
      WHERE id NOT IN (
          SELECT o.id
          FROM orders
                   JOIN operand o on orders.customer_id = o.uuid
          UNION ALL
          SELECT person_id
          FROM employee
          UNION ALL
          SELECT o.id
          FROM calendar_entry_order_info
                   JOIN operand o on calendar_entry_order_info.customer_id = o.uuid
          UNION ALL
          SELECT o.id
          FROM income
                   JOIN operand o on income.supplier_id = o.uuid
          UNION ALL
          SELECT o.id
          FROM order_item_service
                   JOIN operand o on order_item_service.worker_id = o.uuid
          UNION ALL
          SELECT supplier_id
          FROM order_item_part
          WHERE supplier_id IS NOT NULL
      )) TO '/var/lib/postgresql/data/operand.sql';
DELETE
FROM operand
WHERE id NOT IN (
    SELECT o.id
    FROM orders
             JOIN operand o on orders.customer_id = o.uuid
    UNION ALL
    SELECT person_id
    FROM employee
    UNION ALL
    SELECT o.id
    FROM calendar_entry_order_info
             JOIN operand o on calendar_entry_order_info.customer_id = o.uuid
    UNION ALL
    SELECT o.id
    FROM income
             JOIN operand o on income.supplier_id = o.uuid
    UNION ALL
    SELECT o.id
    FROM order_item_service
             JOIN operand o on order_item_service.worker_id = o.uuid
    UNION ALL
    SELECT supplier_id
    FROM order_item_part
    WHERE supplier_id IS NOT NULL
);