Создание серии инкрементных различий в таблицах SQL?
предположим, что у меня есть таблица main, и я делаю два периодических снимка, t0 и t1. Строки никогда не удаляются из main; у него есть столбец is_deleted для мягкого удаления.
create table t0 as select * from main;
-- wait 1 day
create table t1 as select * from main;
Q1. эта команда создает инкрементный снимок изменений основной таблицы?
create table main_diffs as
select * from t1 except
select * from t0;
Q2. Восстанавливают ли эти команды состояние main на момент моментального снимка t1 (без индексов и т. д.)?
create table main_reconstituted as select * from t0;
insert into main_reconstituted select * from main_diffs;
И так далее, каждый день захватывая таблицу $ t_{n} $ на основе $ t{n-1} $?
Контекст: ежедневная запись изменений большой таблицы для распространения по сети с ограниченной пропускной способностью.
Задействованные базы данных: postgresql, sqlite, duckdb.
@a_horse_with_no_name, я действительно использую sqlite, duckdb и postgresql.
Ответы 1
Ответ на оба вопроса «может быть».
Для Q1, если в структуру таблицы не вносятся изменения, включая: порядок столбцов, добавление или удаление столбцов или типы данных столбцов; то запрос приводит к добавочным различиям на уровне строк.
Для Q2, если ограничения, перечисленные выше для Q1, соблюдены и строки вставляются, но никогда не обновляются, то конечным результатом будет то, что main_reconstituted имеет то же содержимое, что и main. Если обновления на main разрешены и есть подходящий уникальный ключ, то можно использовать INSERT ... ON CONFLICT UPDATE ... для достижения желаемого результата.
Избегайте использования * в запросах, особенно когда порядок столбцов может повлиять на результаты. Если обновления не разрешены, а первичный ключ является монотонно возрастающим значением, то main_diff также можно создать более эффективно, выбрав все строки в main, значения первичного ключа которых превышают максимальное значение в моментальном снимке предыдущего дня.
Я удалил конфликтующие теги СУБД. Пожалуйста, добавьте только один тег для продукта базы данных, который вы действительно используете.