Skip to content

Instantly share code, notes, and snippets.

@u-mulder
Created July 6, 2019 20:10
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save u-mulder/e0359477861b3e63de87388655426e01 to your computer and use it in GitHub Desktop.
Save u-mulder/e0359477861b3e63de87388655426e01 to your computer and use it in GitHub Desktop.
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