Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Transfer data from certain postgresql table fields to single hstore field
<?php
$fields = [
'field_1',
'field_2',
'field_3',
'date_field',
'field_6',
'field_7',
];
$glue = '~~~';
$selectPattern = "(CASE WHEN (%s IS NOT NULL%s) THEN ('%s' || '$glue' || %s || '$glue') ELSE '' END)";
$select = [];
foreach ($fields as $field) {
$replacements = array_fill(0, 4, $field);
if ('date_field' === $field) {
$replacements[1] = '';
$replacements[3] = "TO_CHAR(date_field, 'YYYY-MM-DD')";
} else {
$replacements[1] = " AND $field != ''";
}
$select[] = vsprintf($selectPattern, $replacements);
}
$select = implode(' || ', $select);
foreach (['table_1', 'table_2'] as $table) {
executeSql("
UPDATE {$table}
SET attributes = hstore(string_to_array(rtrim(($select), '~'), '{$glue}'))
WHERE some_field != 'someValue'
");
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.