Closed Piiit closed 2 years ago
@rcavaliere I have the query to fix the measurements:
with newval as (
select m.id, string_value
from measurementstringhistory m
join provenance p on p.id = m.provenance_id
where p.data_collector = 'odh-mobility-dc-vms-a22'
and string_value ~ '([0-9]+?\|)+?[0-9]+?'
)
update measurementstringhistory m
set string_value = array_to_string(array(select distinct unnest(string_to_array(newval.string_value, '|')) order by 1), '|')
from newval
where m.id = newval.id;
This will create single numbers (as strings) or concatenation of numbers like 1|2
, if we have more than one distinct number inside. The ordering of the number is not preserved, is that a problem? For example, 2|1
becomes 1|2
will always be sorted in a ascending manner.
@Piiit that's not an issue, we can proceed!
@rcavaliere Done, we updated 3661043 records...
Please test on production and eventually reopen this issue
See comments in #439 for details