🪴 XQZmeSIR

Staging Tables in PostgreSQL

2 min read

Staging Tables в PostgreSQL: зачем и как

Что это такое?

Staging table — это временная или постоянная таблица в базе данных, предназначенная для «сырого» приема данных из внешних источников (CSV, JSON, API). В ней данные хранятся в максимально простом виде (обычно всё в text), чтобы избежать ошибок при импорте.

Почему это важно?

Если ты пытаешься загрузить данные сразу в «финальную» таблицу с жесткими типами (integer, date, boolean, foreign keys), любой мусор в CSV (например, буква «о» вместо нуля в поле age) приведет к откату всей транзакции.

Staging-таблица позволяет:

  1. Гарантировать успешный импорт: Ты всегда загрузишь файл, даже если он «грязный».
  2. Разделить ответственность: Сначала ты просто копируешь данные, потом — валидируешь их SQL-запросами.
  3. Упростить отладку: Ты видишь, какие именно строки содержат ошибки, не ломая структуру базы.

Типовой Workflow (Паттерн)

  1. Создание staging-таблицы: Все поля имеют тип text.
  2. Загрузка: Используем \copy для быстрой вставки.
  3. Валидация: Ищем аномалии (пустые значения, неверные форматы).
  4. Трансформация и вставка: Переливаем данные в целевую таблицу с использованием INSERT INTO ... SELECT ... и приведением типов (::integer, ::date, NULLIF).
  5. Очистка: Удаляем данные из staging-таблицы (или саму таблицу).

Пример реализации

1. Staging-слой

CREATE TABLE staging_users (
    raw_id text,
    raw_name text,
    raw_age text
);

2. Финальный слой

CREATE TABLE users (
    id integer PRIMARY KEY,
    name text NOT NULL,
    age integer
);

3. Перелив с очисткой

INSERT INTO users (id, name, age)
SELECT 
    raw_id::integer,
    trim(raw_name),
    NULLIF(raw_age, '')::integer
FROM staging_users
WHERE raw_id ~ '^\d+$'; -- Фильтруем только валидные ID

Когда использовать?

  • Всегда, если данные приходят из внешних источников (клиенты, парсеры, Excel-файлы).
  • Всегда, если ты не уверен в качестве данных на 100%.
  • Не обязательно, если ты работаешь с данными, которые сам же и выгрузил из этой же базы (они уже чистые).

Полезные SQL-приемы для Staging

  • NULLIF(col, '') — превращает пустую строку в NULL.
  • TRIM(col) — убирает лишние пробелы по краям.
  • ~ '^\d+$' — проверка регулярным выражением (является ли строка числом).
  • COALESCE(col, 'default_value') — замена NULL на значение по умолчанию.

Reference:

  • ChatGPT

Graph View

Backlinks