convert varchar[ ] column type to uuid[ ]

there is a column of type character varying[] , which contains data of type uuid like {0f071799-e37e-4e1c-9620-e580447416fe,913a7134-6092-45fa-ae18-163302db8112},

but there are also some old values of another type like {5edfd4edfa1bb21a142442a0}.

How can the column type be converted? I used the script:

alter table services alter column office_ids type uuid[] USING office_ids::uuid[];

but gives an error – invalid syntax for type uuid: “5edfd4edfa1bb21a142442a0”.

Answer

You must first convert your 25 character values into valid uuid values.

One such conversion would be:

8f5f7cc46821423fa6057025a -> 00000008-f5f7-cc46-8214-23fa6057025a

The SQL for this is:

regexp_replace('8f5f7cc46821423fa6057025a', '^(.)(.{4})(.{4})(.{4})(.{12})^', '00000001-2-3-4-5')

output:

00000008-f5f7-cc46-8214-23fa6057025a

Which leaves valid uuids unchanged. See live demo.

You can use this to update the bad values like this:

update services set office_ids = array(
  select regexp_replace(t.val, '^(.)(.{4})(.{4})(.{4})(.{12})$', '00000001-2-3-4-5')
  from unnest(services.office_ids) as t(val)
)

Then your alter command will work.

See live demo.