Skip to content
Advertisement

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”.

Advertisement

Answer

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

One such conversion would be:

The SQL for this is:

output:

Which leaves valid uuids unchanged. See live demo.

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

Then your alter command will work.

See live demo.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement