I have a table with an array of URLs in column urls. Now there's a need to update some URLs changing the hostname. E.g.:
{https://storage.host1.com/file.jpg, https://storage.host1.com/file2.jpg}
should become:
{https://data.newhost.net/file.jpg, https://data.newhost.net/file2.jpg}
Got stuck on array elements string replacement.
To only replace the leading hostname - and only update rows where anything changes to begin with:
UPDATE tbl t
SET urls = t1.urls
FROM (
SELECT t.id, u.*
FROM tbl t
LEFT JOIN LATERAL (
SELECT bool_or(true) FILTER (WHERE u.url ^@ 'https://storage.host1.com/') AS hit
, array_agg(regexp_replace(url, '^https://storage.host1.com/', 'https://data.newhost.net/')) AS urls
FROM unnest(t.urls) u(url)
) u ON true
) t1
WHERE t.id = t1.id
AND t1.hit;
Since elements are re-aggregated right after being unnested, original order should be preserved. But see:
Pretty expensive, as all arrays are unnested, each element is checked, and then all arrays are re-aggregated. There are no clean & easy index options to identify qualifying rows cheaply, and only process those. (You might use a workaround like a trigram index on the text representation of the array ...)
If you have to do this often, consider normalizing your DB design, with a 1:n relationship to a separate urls table. Would make this operation much simpler and faster.
