Подготовка БД

Перед началом работы развернем следующую тестовую базу данных, название можете выбрать произвольно:

-- Основная таблица сотрудников
CREATE TABLE TAB1 (
    ID SERIAL PRIMARY KEY,
    FAM VARCHAR(50),
    IMJ VARCHAR(50),
    OTCH VARCHAR(50),
    DT_ROJD DATE,
    POL CHAR(1),
    ADDRESS_REG TEXT,
    ADDRESS_WORK TEXT
);

-- Временная таблица для корректировки и проверки данных
CREATE TABLE TAB1_TEMP (
    ID SERIAL PRIMARY KEY,
    FAM VARCHAR(50),
    IMJ VARCHAR(50),
    OTCH VARCHAR(50),
    DT_ROJD DATE,
    POL CHAR(1),
    ADDRESS_REG TEXT,
    ADDRESS_WORK TEXT
)

-- Таблица FPSP - в ней хранятся записи о пропусках (CARDNO) выданных сотрудникам (ID)
CREATE TABLE FPSP (
    ID INT,
    CARDNO VARCHAR(20),
    SOME_DATA VARCHAR(100)
);

Загрузка данных первого филиала

Вы получили от филиала организации CSV-файл data.csv, содержащий данные о сотрудниках. В сообщении было написано, что вероятно, они были введены вручную в Excel и, потенциально, могут содержать ошибки и смесь различных форматов данных. Пример данных, которые потенциально могут находиться в полученном .csv файле:

FAM,IMJ,OTCH,DT_ROJD,POL,ADDRESS_REG,ADDRESS_WORK
Игнатова,Варвара,Валериевна,2015-11-14,m,,"ст. Джейрах, наб. Котовского, д. 9, 889080"
ЛихачевНиканорВитальевич,,,2012-12-23,м,,"г. Псков, ш. Декабристов, д. 4 стр. 48, 352194"
...

Чтобы справиться с этим, вы решаете сначала загрузить данные во временную таблицу TAB1_TEMP. Для этого воспользуемся следующей командой:

COPY STAGE_TAB1(FAM, IMJ, OTCH, DT_ROJD, POL, ADDRESS_REG, ADDRESS_WORK)
FROM '/path/to/data.csv' CSV HEADER ENCODING 'UTF8';

Теперь необходимо очистить данные, обратите внимание на:

  1. Слитное написание FAM,IMJ,OTCH в одном поле - SPLIT_PART;
  2. Несогласованные форматы дат (DD.MM.YYYY, YYYY-MM-DD и другие) - TO_DATE;
  3. Пустые значения в столбцах;
  4. Опечатки в поле POL (муж, жен, m, жe) - LOWER & IN;
  5. Дублирующиеся записи - GROUP BY & DISTINCT;
  6. Ошибки при заполнении поля DT_ROJD (даты в будущем или невозможные даты)
  7. Некорректные символы или пустые строки в текстовых полях

Массовая вставка из одной таблицы в другую (TAB1_TEMP -> TAB1)

Когда данные очищены, вы можете массово перенести их в TAB1:

INSERT INTO TAB1 (FAM, IMJ, OTCH, DT_ROJD, POL, ADDRESS_REG, ADDRESS_WORK)
SELECT FAM, IMJ, OTCH, DT_ROJD, POL, ADDRESS_REG, ADDRESS_WORK FROM TAB1_TEMP;

Теперь в TAB1 находятся корректные данные из первого филиала.

Массовая вставка нескольких строк напрямую

Помимо данных из филиалов, иногда нужно быстро добавить несколько записей вручную. Для этого можно использовать массовую вставку одним оператором INSERT ... VALUES:

INSERT INTO TAB1 (FAM, IMJ, OTCH, DT_ROJD, POL) VALUES
    ('T1', 'T1', 'T1', '2024-10-18', 'м'),
    ('T2', 'T2', 'T2', '2024-10-18', 'м'),
    ('T3', 'T3', 'T3', '2024-10-18', 'м');

Задача: при помощи генератора или вручную написать 10 новых записей для добавления в таблицу.

Получение данных второго филиала и проверка через EXCEPT

Через некоторое время от второго филиала поступает уже подготовленный специалистом CSV-файл data2.csv. Его данные должны быть загружены во временную таблицу TAB1_TEMP. Но прежде очистите TAB1_TEMP от прежних данных, чтобы избежать наложения:

TRUNCATE TABLE TAB1_TEMP;

Теперь загрузите новый CSV:

COPY TAB1_TEMP(FAM, IMJ, OTCH, DT_ROJD, POL, ADDRESS_REG, ADDRESS_WORK)
FROM '/path/to/data2.csv' CSV HEADER ENCODING 'UTF8';

Данные второго филиала уже корректны, но вы должны убедиться, что в TAB1 их ещё нет. Для этого можно воспользоваться оператором EXCEPT, задача которого найти расхождение между таблицами:

SELECT FAM, IMJ, OTCH, DT_ROJD, POL, ADDRESS_REG, ADDRESS_WORK
FROM TAB1_TEMP
EXCEPT
SELECT FAM, IMJ, OTCH, DT_ROJD, POL, ADDRESS_REG, ADDRESS_WORK
FROM TAB1;

Если результатом запроса будут строки, значит они присутствуют в TAB1_TEMP, но отсутствуют в TAB1. Можно их массово добавить при помощи комбинации массовой вставки и EXCEPT, так как предполагается, что предыдущий специалист их уже подготовил:

INSERT INTO TAB1 (FAM, IMJ, OTCH, DT_ROJD, POL, ADDRESS_REG, ADDRESS_WORK)
SELECT FAM, IMJ, OTCH, DT_ROJD, POL, ADDRESS_REG, ADDRESS_WORK
FROM TAB1_TEMP
EXCEPT
SELECT FAM, IMJ, OTCH, DT_ROJD, POL, ADDRESS_REG, ADDRESS_WORK
FROM TAB1;

Использование множественного IN по нескольким полям

Предположим, в таблице FPSP (файл с данными) хранится информация о пропусках для сотрудников: (ID, CARDNO). Если вы хотите вставить в TAB1 данные только о тех сотрудниках, которым уже выдан пропуск, вы можете использовать множественный IN. Например, при переносе сотрудников из TAB2:

INSERT INTO TAB1 (FAM, IMJ, OTCH, DT_ROJD, POL, ADDRESS_REG, ADDRESS_WORK)
SELECT T2.FAM, T2.IMJ, T2.OTCH, T2.DT_ROJD, T2.POL, T2.ADDRESS_REG, T2.ADDRESS_WORK
FROM TAB2 T2
WHERE (T2.ID, T2.CARDNO) IN (
    SELECT ID, CARDNO FROM FPSP
);

Автоматизация

В реальной практике вы можете использовать хранимые процедуры и триггеры на BEFORE INSERT, чтобы автоматически очищать данные при загрузке в TAB1. Тогда не придётся вручную удалять некорректные строки или преобразовывать даты — всё будет происходить автоматически.

Можно реализовать на доп. баллы, передам Носову, что сделали дополнительный усложненный вариант и можете претендовать на дополнительный + на экзамене.

Таким образом, вы последовательно:

  • Обнаружили проблему при прямой загрузке «грязных» данных.
  • Решили её, использовав промежуточную таблицу для загрузки и очистки, а затем выполнили массовую вставку в основную таблицу.
  • Проверили уникальность новых данных с помощью EXCEPT.
  • Освоили массовую вставку из одной таблицы в другую.
  • Применили множественный IN по нескольким полям.
  • Узнали о возможностях автоматизации процесса.