Оператор UPDATE в PostgreSQL: полное руководство по изменению данных

Информация о материале: Автор: Андрей К.; Категория: Scripts; Опубликовано: 04 февраля 2026; Просмотров: 551

Оператор UPDATE в PostgreSQL — это важный инструмент для модификации существующих записей в таблицах. В этой статье мы подробно разберем все аспекты использования UPDATE: от базового синтаксиса до сложных сценариев с подзапросами, транзакциями и возвратом обновленных данных.

Подготовка: структура таблицы 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,
    hire_date DATE NOT NULL,
    salary DECIMAL(10, 2),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
);

Базовый синтаксис оператора UPDATE

Основная структура команды

UPDATE table_name
SET column1 = value1, 
    column2 = value2,
    ...
WHERE condition;

Важное предупреждение: Без условия WHERE обновятся ВСЕ записи в таблице!

Пример 1: Базовое обновление одного поля

Задача: Изменить зарплату конкретного сотрудника

UPDATE employees
SET salary = 60000.00,
    updated_at = CURRENT_TIMESTAMP
WHERE employee_id = 14;

Разбор запроса:

UPDATE employees — указываем таблицу для обновления
SET salary = 60000.00 — устанавливаем новое значение зарплаты
updated_at = CURRENT_TIMESTAMP — обновляем метку времени изменения
WHERE employee_id = 14 — условие: только сотрудник с ID 14

Проверка результата:

SELECT employee_id, first_name, last_name, salary, updated_at
FROM employees
WHERE employee_id = 14;

Пример 2: Обновление на основе текущих значений

Задача: Увеличить зарплату на 10% для всех активных сотрудников

UPDATE employees
SET salary = salary * 1.10,
    updated_at = CURRENT_TIMESTAMP
WHERE is_active = TRUE;

Что происходит:

salary * 1.10 — увеличиваем текущую зарплату на 10%
WHERE is_active = TRUE — обновляем только активных сотрудников

Альтернативный вариант с округлением:

UPDATE employees
SET salary = ROUND(salary * 1.10, 2),
    updated_at = CURRENT_TIMESTAMP
WHERE is_active = TRUE;

Пример 3: Обновление с подзапросами

Задача: Установить сотрудникам с фамилией 'Сергеев' максимальную зарплату среди всех активных сотрудников

UPDATE employees
SET salary = (
    SELECT MAX(salary) 
    FROM employees 
    WHERE is_active = TRUE
),
updated_at = CURRENT_TIMESTAMP
WHERE last_name = 'Сергеев'
  AND is_active = TRUE;

Пошаговое выполнение:

Подзапрос (SELECT MAX(salary)...) выполняется первым
Возвращает максимальную зарплату среди активных сотрудников
Основной UPDATE использует это значение для обновления

Более безопасный вариант с проверкой:

UPDATE employees e1
SET salary = (
    SELECT MAX(salary) 
    FROM employees 
    WHERE is_active = TRUE
),
updated_at = CURRENT_TIMESTAMP
WHERE EXISTS (
    SELECT 1 
    FROM employees e2 
    WHERE e2.last_name = 'Сергеев' 
      AND e2.is_active = TRUE
      AND e1.employee_id = e2.employee_id
);

Пример 4: Массовое обновление с оператором IN

Задача: Деактивировать несколько сотрудников

UPDATE employees
SET is_active = FALSE,
    updated_at = CURRENT_TIMESTAMP
WHERE employee_id IN (3000, 3001, 3002);

Альтернативный вариант с диапазоном:

UPDATE employees
SET is_active = FALSE,
    updated_at = CURRENT_TIMESTAMP
WHERE employee_id BETWEEN 3000 AND 3002;

Обновление с подзапросом в IN:

UPDATE employees SET is_active = FALSE, updated_at = CURRENT_TIMESTAMP WHERE employee_id IN ( SELECT employee_id FROM employees WHERE hire_date < '2023-01-01' AND salary < 50000 );

Пример 5: Дифференцированное обновление с оператором CASE

Задача: Применить разный процент повышения зарплаты в зависимости от текущего уровня

UPDATE employees
SET salary = CASE
    WHEN salary < 40000 THEN salary * 1.15  -- 15% для низких зарплат
    WHEN salary BETWEEN 40000 AND 70000 THEN salary * 1.10  -- 10%
    WHEN salary > 70000 THEN salary * 1.05  -- 5% для высоких зарплат
    ELSE salary
END,
updated_at = CURRENT_TIMESTAMP
WHERE is_active = TRUE;

Расширенный пример с несколькими условиями:

UPDATE employees
SET salary = CASE
    WHEN salary < 40000 AND years_of_service > 5 THEN salary * 1.20
    WHEN salary < 40000 THEN salary * 1.15
    WHEN salary BETWEEN 40000 AND 70000 AND performance_score > 80 THEN salary * 1.12
    WHEN salary BETWEEN 40000 AND 70000 THEN salary * 1.10
    WHEN salary > 70000 AND performance_score > 90 THEN salary * 1.08
    WHEN salary > 70000 THEN salary * 1.05
    ELSE salary
END,
bonus_eligibility = CASE
    WHEN salary * 1.10 > 80000 THEN TRUE
    ELSE FALSE
END,
updated_at = CURRENT_TIMESTAMP
WHERE is_active = TRUE;

Пример 6: Возврат обновленных данных с RETURNING

Особенность PostgreSQL: возможность получить обновленные данные сразу

UPDATE employees
SET salary = salary * 1.10,
    updated_at = CURRENT_TIMESTAMP
WHERE department_id = 3
  AND is_active = TRUE
RETURNING 
    employee_id,
    first_name,
    last_name,
    salary AS new_salary,
    (salary / 1.10) AS old_salary,
    ((salary / 1.10) * 0.10) AS increase_amount;

Результат будет содержать:

 employee_id | first_name | last_name | new_salary | old_salary | increase_amount
-------------|------------|-----------|------------|------------|-----------------
      101    | Иван       | Петров    |  55000.00  |  50000.00  |   5000.00
      102    | Мария      | Сидорова  |  66000.00  |  60000.00  |   6000.00

Сохранение результатов в таблицу:

UPDATE employees
SET salary = salary * 1.10,
    updated_at = CURRENT_TIMESTAMP
WHERE is_active = TRUE
RETURNING 
    employee_id,
    first_name,
    last_name,
    salary AS new_salary
INTO TEMP TABLE salary_update_log;

Пример 7: Обновление с использованием CTE (Common Table Expressions)

Задача: Обновить только первые три записи с наибольшей зарплатой

WITH top_employees AS (
    SELECT employee_id
    FROM employees
    WHERE is_active = TRUE
    ORDER BY salary DESC
    LIMIT 3
)
UPDATE employees e
SET salary = salary * 1.15,
    updated_at = CURRENT_TIMESTAMP
FROM top_employees te
WHERE e.employee_id = te.employee_id;

Более сложный пример CTE:

WITH department_stats AS (
    SELECT 
        department_id,
        AVG(salary) as avg_salary,
        COUNT(*) as employee_count
    FROM employees
    WHERE is_active = TRUE
    GROUP BY department_id
),
underpaid_employees AS (
    SELECT 
        e.employee_id,
        e.salary,
        ds.avg_salary,
        (ds.avg_salary - e.salary) as salary_gap
    FROM employees e
    JOIN department_stats ds ON e.department_id = ds.department_id
    WHERE e.salary < ds.avg_salary * 0.8
      AND e.is_active = TRUE
)
UPDATE employees e
SET salary = ue.avg_salary,
    updated_at = CURRENT_TIMESTAMP
FROM underpaid_employees ue
WHERE e.employee_id = ue.employee_id
RETURNING 
    e.employee_id,
    e.first_name,
    e.last_name,
    ue.salary AS old_salary,
    e.salary AS new_salary,
    ue.salary_gap AS corrected_gap;

Пример 8: Безопасное обновление с транзакциями

Продвинутые техники обновления

Техника 1: Обновление с объединением таблиц (JOIN)

UPDATE employees e
SET 
    salary = d.target_salary,
    department_id = d.new_department_id
FROM department_updates d
WHERE e.employee_id = d.employee_id
  AND e.is_active = TRUE;

Техника 2: Обновление на основе агрегатных функций

UPDATE employees e
SET 
    salary_rank = sub.rank,
    department_avg_salary = sub.avg_dept_salary
FROM (
    SELECT 
        employee_id,
        RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank,
        AVG(salary) OVER (PARTITION BY department_id) as avg_dept_salary
    FROM employees
    WHERE is_active = TRUE
) sub
WHERE e.employee_id = sub.employee_id;

Техника 3: Обновление с обработкой JSON данных

UPDATE employees
SET 
    contact_info = contact_info || '{"phone": "+7-999-123-45-67"}',
    skills = skills || '["PostgreSQL", "Python"]'::jsonb,
    updated_at = CURRENT_TIMESTAMP
WHERE employee_id = 42;

Безопасность и валидация при обновлении

Валидация данных перед обновлением

CREATE OR REPLACE FUNCTION safe_salary_update(
    p_employee_id INTEGER,
    p_new_salary DECIMAL
)
RETURNS VOID AS $$
DECLARE
    v_current_salary DECIMAL;
    v_max_increase_percent DECIMAL := 50.0;
BEGIN
    -- Получение текущей зарплаты
    SELECT salary INTO v_current_salary
    FROM employees
    WHERE employee_id = p_employee_id
      AND is_active = TRUE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Сотрудник не найден или неактивен';
    END IF;

    -- Проверка увеличения зарплаты
    IF (p_new_salary - v_current_salary) / v_current_salary * 100 > v_max_increase_percent THEN
        RAISE EXCEPTION 'Увеличение зарплаты более чем на % процентов запрещено', 
                        v_max_increase_percent;
    END IF;

    -- Проверка минимальной зарплаты
    IF p_new_salary < 30000 THEN
        RAISE EXCEPTION 'Зарплата не может быть меньше 30000';
    END IF;

    -- Выполнение обновления
    UPDATE employees
    SET salary = p_new_salary,
        updated_at = CURRENT_TIMESTAMP
    WHERE employee_id = p_employee_id;

    -- Логирование
    INSERT INTO salary_change_log (
        employee_id,
        old_salary,
        new_salary,
        changed_by,
        change_date
    ) VALUES (
        p_employee_id,
        v_current_salary,
        p_new_salary,
        CURRENT_USER,
        CURRENT_TIMESTAMP
    );

END;
$$ LANGUAGE plpgsql;

Использование триггеров для аудита изменений

CREATE TABLE employee_audit (
    audit_id SERIAL PRIMARY KEY,
    employee_id INTEGER,
    changed_column VARCHAR(50),
    old_value TEXT,
    new_value TEXT,
    changed_by VARCHAR(100),
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION log_employee_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.salary != NEW.salary THEN
        INSERT INTO employee_audit (
            employee_id,
            changed_column,
            old_value,
            new_value,
            changed_by
        ) VALUES (
            NEW.employee_id,
            'salary',
            OLD.salary::TEXT,
            NEW.salary::TEXT,
            CURRENT_USER
        );
    END IF;

    IF OLD.is_active != NEW.is_active THEN
        INSERT INTO employee_audit (
            employee_id,
            changed_column,
            old_value,
            new_value,
            changed_by
        ) VALUES (
            NEW.employee_id,
            'is_active',
            OLD.is_active::TEXT,
            NEW.is_active::TEXT,
            CURRENT_USER
        );
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_employee_audit
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_employee_changes();

Оптимизация производительности UPDATE

Рекомендация 1: Использование индексов для условий WHERE

-- Создание индексов для часто используемых условий
CREATE INDEX idx_employees_active ON employees(is_active);
CREATE INDEX idx_employees_department ON employees(department_id);
CREATE INDEX idx_employees_salary ON employees(salary);

Рекомендация 2: Пакетное обновление для больших объемов

-- Обновление по частям
DO $$
DECLARE
    batch_size INTEGER := 1000;
    offset_val INTEGER := 0;
    total_updated INTEGER := 0;
BEGIN
    LOOP
        UPDATE employees
        SET salary = salary * 1.05
        WHERE employee_id IN (
            SELECT employee_id
            FROM employees
            WHERE is_active = TRUE
            ORDER BY employee_id
            LIMIT batch_size
            OFFSET offset_val
        );

        GET DIAGNOSTICS total_updated = ROW_COUNT;
        offset_val := offset_val + batch_size;
        
        EXIT WHEN total_updated = 0;
        
        -- Пауза для снижения нагрузки
        PERFORM pg_sleep(0.1);
    END LOOP;
END;
$$;

Рекомендация 3: Отключение триггеров при массовом обновлении

-- Временное отключение триггеров
ALTER TABLE employees DISABLE TRIGGER ALL;

-- Массовое обновление
UPDATE employees
SET salary = salary * 1.10
WHERE is_active = TRUE;

-- Включение триггеров обратно
ALTER TABLE employees ENABLE TRIGGER ALL;

Лучшие практики работы с UPDATE

Практика 1: Всегда используйте WHERE

-- НЕПРАВИЛЬНО (обновит все записи!):
UPDATE employees SET salary = 50000;

-- ПРАВИЛЬНО:
UPDATE employees 
SET salary = 50000
WHERE employee_id = 42;

Практика 2: Тестируйте условия SELECT перед UPDATE

-- Сначала проверьте, какие записи будут обновлены
SELECT COUNT(*), employee_id, first_name, last_name
FROM employees
WHERE department_id = 3
  AND hire_date < '2023-01-01';

-- Затем выполняйте UPDATE
UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 3
  AND hire_date < '2023-01-01';

Практика 3: Используйте транзакции для связанных операций

BEGIN;

UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 5;

UPDATE department_budgets
SET salary_budget = salary_budget * 1.10
WHERE department_id = 5;

INSERT INTO budget_audit (
    department_id,
    old_budget,
    new_budget,
    change_date
) VALUES (
    5,
    (SELECT salary_budget FROM department_budgets WHERE department_id = 5 FOR UPDATE),
    (SELECT salary_budget * 1.10 FROM department_budgets WHERE department_id = 5),
    CURRENT_TIMESTAMP
);

COMMIT;

Практика 4: Ведение журнала изменений

-- Создание таблицы для журналирования
CREATE TABLE update_history (
    history_id SERIAL PRIMARY KEY,
    table_name VARCHAR(100),
    operation VARCHAR(10),
    old_data JSONB,
    new_data JSONB,
    executed_by VARCHAR(100),
    executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Функция для логирования
CREATE OR REPLACE FUNCTION log_update_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO update_history (
        table_name,
        operation,
        old_data,
        new_data,
        executed_by
    ) VALUES (
        TG_TABLE_NAME,
        TG_OP,
        row_to_json(OLD),
        row_to_json(NEW),
        CURRENT_USER
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Мониторинг и отладка операций UPDATE

Просмотр последних обновлений

-- Недавние обновления зарплаты
SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    sc.old_salary,
    sc.new_salary,
    sc.change_date,
    sc.changed_by
FROM employees e
JOIN salary_change_log sc ON e.employee_id = sc.employee_id
ORDER BY sc.change_date DESC
LIMIT 10;

Анализ производительности UPDATE

-- Статистика выполнения UPDATE-запросов
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements
WHERE query LIKE 'UPDATE%employees%'
ORDER BY total_time DESC
LIMIT 10;

Отслеживание блокировок

-- Активные блокировки при UPDATE
SELECT 
    pid,
    usename,
    query,
    age(clock_timestamp(), query_start) as query_age,
    wait_event_type,
    wait_event
FROM pg_stat_activity
WHERE query LIKE 'UPDATE%'
  AND state = 'active'
ORDER BY query_start;

Заключение

Оператор UPDATE в PostgreSQL — это гибкий и эффективный инструмент для изменения существующих данных. Ключевые моменты для успешной работы:

Всегда используйте WHERE — чтобы избежать обновления всех записей
Тестируйте условия — сначала SELECT, потом UPDATE
Используйте транзакции — для согласованности важных изменений
Применяйте RETURNING — для получения обновленных данных
Ведите аудит — для отслеживания изменений
Оптимизируйте производительность — через индексы и пакетную обработку

Начните с простых обновлений, постепенно осваивая более сложные сценарии с подзапросами, CTE и транзакциями. Помните, что грамотное использование UPDATE — залог целостности и согласованности данных в вашей базе.

В следующих материалах мы изучим оператор DELETE для удаления данных и создание триггеров для автоматизации бизнес-логики.

Статья подготовлена по материалам обучающего видео для вашего удобства.

Оптимизация SQL

Полезные скрипты

Использование PGAdmin

Сквозной пример в PostgreSQL

Видеомонтаж

Использование нейросетей

Интерактивные материалы

Офисные приложения