CODEWITHSUNDEEP
arrayspostgresql
triunenature
triunenature

Reputation: 671

Fix poorly inserted Postgres Array

The question is based on the answer from: Postgres SELECT where value in array

I have a column that has the values:

{'V1', 'V2', "'V3 With Space'"}

It should look like:

{V1, V2, "V3 With Space"}

There are only 15 possible choices within the array. I could solve this by iterating over the entire db in my language of choice, unpacking the array, then updating it correctly. However, as there are 1.5 million records, the transaction time would take ~2 days. (to download, then re-upload all records)

The question is:

Is there any way of using an update command to fix this directly, without reparsing all records

Here's what I've been trying to do:

UPDATE tbl SET col1 = {V1} WHERE col1 = {'V1'}

The problem is, there are a lot of permutations available. I could see an update where any, but I dont know how to them remove the new offending record from the list

Upvotes: 1

Views: 45

Answers (1)

Nick Barnes
Nick Barnes

Reputation: 21346

You can unnest the array to give a table, trim the leading/trailing single quotes, and aggregate it back in to an array:

UPDATE tbl SET col1 = (
  SELECT array_agg(trim(x, ''''))
  FROM UNNEST(col1) u(x)
)

Upvotes: 4

Related Questions