Импорт данных в PostgreSQL через pgAdmin: полное руководство для эффективной загрузки данных

-- Таблица employees для импорта данных
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,           -- Автоинкрементный идентификатор
    first_name VARCHAR(50) NOT NULL,          -- Имя сотрудника
    last_name VARCHAR(50) NOT NULL,           -- Фамилия сотрудника
    email VARCHAR(100) UNIQUE NOT NULL,       -- Уникальный email
    hire_date DATE NOT NULL,                  -- Дата приема на работу
    salary NUMERIC(10, 2) CHECK (salary > 0), -- Заработная плата
    department_id INTEGER,                     -- Идентификатор отдела
    is_active BOOLEAN DEFAULT TRUE,           -- Статус активности
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Дата создания записи
);

-- Комментарии для документации
COMMENT ON TABLE employees IS 'Таблица сотрудников компании';
COMMENT ON COLUMN employees.employee_id IS 'Автоинкрементный уникальный идентификатор';
COMMENT ON COLUMN employees.email IS 'Уникальный email сотрудника (логин)';

first_name,last_name,email,hire_date,salary,department_id,is_active
Иван,Петров,Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.,2024-01-15,75000.00,3,true
Мария,Сидорова,Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.,2023-11-20,82000.00,2,true
Алексей,Кузнецов,Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.,2024-03-10,68000.00,1,true
Ольга,Васильева,Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.,2022-07-15,95000.00,4,true
Дмитрий,Смирнов,Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.,2024-02-28,58000.00,2,true
Екатерина,Попова,Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.,2023-09-12,62000.00,1,true
Сергей,Новиков,Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.,2021-12-01,105000.00,3,true
Анна,Морозова,Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.,2024-04-05,71000.00,2,true

import csv
import sys

def validate_csv(file_path, expected_columns):
    """Проверка CSV-файла перед импортом в PostgreSQL"""
    try:
        with open(file_path, 'r', encoding='utf-8') as file:
            # Проверка кодировки
            sample = file.read(1024)
            file.seek(0)
            
            # Определение разделителя
            dialect = csv.Sniffer().sniff(sample)
            
            reader = csv.reader(file, dialect)
            headers = next(reader)
            
            # Проверка заголовков
            if headers != expected_columns:
                print(f"Ошибка: Заголовки не совпадают")
                print(f"Ожидалось: {expected_columns}")
                print(f"Получено: {headers}")
                return False
            
            # Проверка данных
            row_number = 2  # Начинаем с первой строки данных
            for row in reader:
                if len(row) != len(expected_columns):
                    print(f"Ошибка в строке {row_number}: "
                          f"ожидалось {len(expected_columns)} столбцов, "
                          f"получено {len(row)}")
                    return False
                
                # Проверка email (опционально)
                email_index = expected_columns.index('email')
                if '@' not in row[email_index]:
                    print(f"Предупреждение в строке {row_number}: "
                          f"возможно некорректный email: {row[email_index]}")
                
                row_number += 1
            
            print(f"Файл проверен успешно. Строк: {row_number-2}")
            return True
            
    except Exception as e:
        print(f"Ошибка при проверке файла: {e}")
        return False

# Использование
expected = ['first_name', 'last_name', 'email', 'hire_date', 
            'salary', 'department_id', 'is_active']
validate_csv('employees.csv', expected)

✓ first_name    → first_name
✓ last_name     → last_name  
✓ email         → email
✓ hire_date     → hire_date
✓ salary        → salary
✓ department_id → department_id
✓ is_active     → is_active
✗ employee_id   → [исключен]

-- Проверка количества импортированных записей
SELECT COUNT(*) as total_records FROM employees;

-- Просмотр первых 10 записей
SELECT * FROM employees LIMIT 10;

-- Проверка уникальности email
SELECT email, COUNT(*) as duplicates
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;

-- Проверка диапазона дат
SELECT 
    MIN(hire_date) as earliest_date,
    MAX(hire_date) as latest_date,
    COUNT(*) as total_employees
FROM employees;

-- Проверка целостности данных
SELECT 
    'Всего записей' as metric, COUNT(*)::text as value
UNION ALL
SELECT 'Уникальных email', COUNT(DISTINCT email)::text
UNION ALL
SELECT 'Сотрудников с зарплатой > 0', 
       SUM(CASE WHEN salary > 0 THEN 1 ELSE 0 END)::text
UNION ALL
SELECT 'Средняя зарплата', ROUND(AVG(salary), 2)::text
FROM employees;

-- 1. Отключение индексов перед импортом
DROP INDEX IF EXISTS idx_employees_email;
DROP INDEX IF EXISTS idx_employees_department;

-- 2. Импорт данных
-- (через pgAdmin или COPY команду)

-- 3. Включение индексов после импорта
CREATE INDEX idx_employees_email ON employees(email);
CREATE INDEX idx_employees_department ON employees(department_id);

-- 4. Обновление статистики
ANALYZE employees;

-- Более быстрая альтернатива через Query Tool
COPY employees(first_name, last_name, email, hire_date, salary, department_id, is_active)
FROM '/path/to/employees.csv'
WITH (
    FORMAT csv,
    HEADER true,
    DELIMITER ',',
    ENCODING 'UTF8',
    NULL ''
);

-- С прогресс-баром (требует расширения)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 1. Сохраните Excel как CSV
-- 2. Импортируйте как обычный CSV

-- Или используйте утилиту psql + обработку
-- Предварительная конвертация в командной строке:
-- xlsx2csv employees.xlsx employees.csv

-- Создание временной таблицы для JSON
CREATE TEMP TABLE temp_employees_json (
    data JSONB
);

-- Загрузка JSON файла
COPY temp_employees_json FROM '/path/to/employees.json';

-- Преобразование и вставка в основную таблицу
INSERT INTO employees (first_name, last_name, email, hire_date, salary, department_id, is_active)
SELECT 
    data->>'first_name',
    data->>'last_name',
    data->>'email',
    (data->>'hire_date')::DATE,
    (data->>'salary')::NUMERIC,
    (data->>'department_id')::INTEGER,
    COALESCE((data->>'is_active')::BOOLEAN, true)
FROM temp_employees_json;

-- Очистка временной таблицы
DROP TABLE temp_employees_json;

-- Требует расширения xml2
CREATE EXTENSION IF NOT EXISTS xml2;

-- Создание временной таблицы
CREATE TEMP TABLE temp_employees_xml (
    xml_data XML
);

-- Загрузка XML
COPY temp_employees_xml FROM '/path/to/employees.xml';

-- Парсинг XML и вставка
INSERT INTO employees (first_name, last_name, email, hire_date, salary, department_id, is_active)
SELECT 
    (xpath('/employee/first_name/text()', xml_data))[1]::text,
    (xpath('/employee/last_name/text()', xml_data))[1]::text,
    (xpath('/employee/email/text()', xml_data))[1]::text,
    (xpath('/employee/hire_date/text()', xml_data))[1]::text::DATE,
    (xpath('/employee/salary/text()', xml_data))[1]::text::NUMERIC,
    (xpath('/employee/department_id/text()', xml_data))[1]::text::INTEGER,
    COALESCE((xpath('/employee/is_active/text()', xml_data))[1]::text::BOOLEAN, true)
FROM temp_employees_xml,
     unnest(xpath('/employees/employee', xml_data)) AS employee(xml_data);

ERROR: invalid input syntax for type integer: "abc"
CONTEXT: COPY employees, line 2, column department_id: "abc"

ERROR: missing data for column "email"
CONTEXT: COPY employees, line 5

ERROR: duplicate key value violates unique constraint "uk_employees_email"
DETAIL: Key (email)=(Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.) already exists.

-- Преобразование форматов дат перед импортом
-- Временная таблица с текстовым полем даты
CREATE TEMP TABLE temp_employees_raw (
    first_name TEXT,
    last_name TEXT,
    email TEXT,
    hire_date_text TEXT,  -- Текст вместо DATE
    salary NUMERIC,
    department_id INTEGER,
    is_active BOOLEAN
);

-- Импорт в текстовом формате
COPY temp_employees_raw FROM ...;

-- Преобразование и вставка
INSERT INTO employees (...)
SELECT 
    first_name,
    last_name,
    email,
    CASE 
        WHEN hire_date_text ~ '^\d{2}/\d{2}/\d{4}$' 
            THEN to_date(hire_date_text, 'DD/MM/YYYY')
        WHEN hire_date_text ~ '^\d{4}-\d{2}-\d{2}$' 
            THEN hire_date_text::DATE
        ELSE NULL
    END as hire_date,
    salary,
    department_id,
    is_active
FROM temp_employees_raw;

#!/bin/bash
# auto_import.sh

# Настройки
DB_NAME="company_db"
DB_USER="postgres"
CSV_FILE="/data/employees.csv"
LOG_FILE="/var/log/pg_import.log"
ERROR_FILE="/var/log/pg_import_errors.log"

# Функция логирования
log_message() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" >> "$LOG_FILE"
}

# Проверка файла
if [ ! -f "$CSV_FILE" ]; then
    log_message "ОШИБКА: Файл $CSV_FILE не найден"
    exit 1
fi

# Предварительная очистка данных
log_message "Начало импорта из $CSV_FILE"

# Импорт через psql
psql -d "$DB_NAME" -U "$DB_USER" << EOF
BEGIN;

-- Создание временной таблицы
CREATE TEMP TABLE temp_employees_import (
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    hire_date DATE,
    salary NUMERIC(10,2),
    department_id INTEGER,
    is_active BOOLEAN
);

-- Импорт в временную таблицу
COPY temp_employees_import FROM '$CSV_FILE' 
WITH (FORMAT csv, HEADER true, DELIMITER ',', ENCODING 'UTF8');

-- Вставка в основную таблицу с проверкой
INSERT INTO employees (first_name, last_name, email, hire_date, salary, department_id, is_active)
SELECT 
    first_name,
    last_name,
    email,
    hire_date,
    salary,
    department_id,
    COALESCE(is_active, true)
FROM temp_employees_import tmp
WHERE NOT EXISTS (
    SELECT 1 FROM employees e 
    WHERE e.email = tmp.email
);

-- Статистика
SELECT 
    'Импортировано записей: ' || COUNT(*) as result
FROM temp_employees_import;

COMMIT;
EOF

if [ $? -eq 0 ]; then
    log_message "Импорт успешно завершен"
else
    log_message "ОШИБКА при импорте"
    exit 1
fi

# Добавьте в crontab (crontab -e)
# Ежедневный импорт в 2:00 ночи
0 2 * * * /path/to/auto_import.sh

# Каждый час с понедельника по пятницу
0 * * * 1-5 /path/to/auto_import.sh

# С логированием вывода
0 2 * * * /path/to/auto_import.sh >> /var/log/import_cron.log 2>&1

-- Мониторинг скорости импорта
CREATE TABLE import_log (
    log_id SERIAL PRIMARY KEY,
    table_name VARCHAR(100),
    rows_imported INTEGER,
    import_duration INTERVAL,
    import_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    source_file VARCHAR(255),
    success BOOLEAN,
    error_message TEXT
);

-- Функция для логирования импорта
CREATE OR REPLACE FUNCTION log_import(
    p_table_name VARCHAR,
    p_rows_imported INTEGER,
    p_duration INTERVAL,
    p_source_file VARCHAR,
    p_success BOOLEAN,
    p_error_message TEXT DEFAULT NULL
)
RETURNS void AS $$
BEGIN
    INSERT INTO import_log (
        table_name,
        rows_imported,
        import_duration,
        source_file,
        success,
        error_message
    ) VALUES (
        p_table_name,
        p_rows_imported,
        p_duration,
        p_source_file,
        p_success,
        p_error_message
    );
END;
$$ LANGUAGE plpgsql;

-- Отчет по импортам
SELECT 
    table_name,
    COUNT(*) as import_count,
    SUM(rows_imported) as total_rows_imported,
    AVG(EXTRACT(EPOCH FROM import_duration)) as avg_duration_seconds,
    MIN(import_timestamp) as first_import,
    MAX(import_timestamp) as last_import,
    SUM(CASE WHEN success THEN 1 ELSE 0 END) as success_count,
    SUM(CASE WHEN NOT success THEN 1 ELSE 0 END) as failure_count
FROM import_log
WHERE import_timestamp >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY table_name
ORDER BY total_rows_imported DESC;