Сегодня опишу вам как решить следующую сверхспецифическую задачу: у нас в БД PostgreSQL есть таблица некоторой структуры:
id | name | attr_1 | attr_2 | attr_3 |
---------------------------------------|
1 | NAME | v1 | v2 | v3 |
Вследствие каких-то причин в проекте решено соединить данные полей
attr_1, attr_2, attr_3 в одно поле
attrs типа
hstore.
Таким образом, новая таблица и запись в ней выглядит так:
id | name | attrs |
---------------------------------------------------------|
1 | NAME | attr_1 => v1, attr_2 => v2, attr_3 => v3 |
Естественно, будем все максимально автоматизировать. Для работы нам пригодятся некоторые функции для работы с hstore и немного php, просто чтобы сформировать общий текст запроса. Общий текст запроса выглядит вот так:
UPDATE tableName
SET attrs =
hstore(
string_to_array(
rtrim(
(CASE WHEN (attr_1 IS NOT NULL) THEN ('attr_1' || '~~~' || attr_1 || '~~~') ELSE '' END)
||
(CASE WHEN (attr_2 IS NOT NULL) THEN ('attr_2' || '~~~' || attr_2 || '~~~') ELSE '' END)
||
(CASE WHEN (attr_3 IS NOT NULL) THEN ('attr_3' || '~~~' || attr_3 || '~~~') ELSE '' END),
'~'
),
'~~~'
)
)
Итак, что же здесь происходит? Начнем с внутренней части.
1. Для значения каждого из полей
attr_1, attr_2, attr_3 мы создаем строку вида
НазваниеПоля~~~ЗначениеПоля~~~ или просто
пустую строку, если значение поля
NULL. Все эти строки объединяем в одну результрующую.
2. Далее нам требуется избавиться от
~ в конце объединенной строки. В этом нам помогает
rtrim.
3. Потом из объединенной строки мы создаем массив, разбивая строку по разделителю
~~~.
4. И, наконец, полученный массив передаем в метод
hstore. Готово.
Отдельно замечу, что разделителем выбран
~~~ потому, что встретить его в значениях полей
attr_1, attr_2, attr_3 невозможно. Если в ваших данных может встречаться такой набор символов - используйте
другой разделитель из более "странных" символов.
С использованием php можно создать такой скрипт генерации и выполнения запроса:
$fields = [
'attr_1',
'attr_2',
'attr_3',
// еще поля
];
$glue = '~~~';
$selectPattern = "(CASE WHEN (%s IS NOT NULL) THEN ('%s' || '$glue' || %s || '$glue') ELSE '' END)";
$select = [];
foreach ($fields as $field) {
$select[] = vsprintf($selectPattern, array_fill(0, 3, $field);
}
$select = implode(' || ', $select);
$this->runSql("
UPDATE {$table}
SET attrs = hstore(string_to_array(rtrim(({$select}), '~'), '{$glue}'))
");
По
ссылке - улучшенный гист с кодом, обрабатывающим даже поле типа
datetime.
