how to build using expression for data conversion when alter column type-CodePudding

I have a column Status with type smallint, which I want to convert it to uuid. The new value should be generated like this:

If the old value equals to 0 then take uuid_nil() as new value
Otherwise, take a random value from uuid_generate_v1()

The convert logic described above could be changed to a simple one if it's not possible to achieve.

I checked the pg docs here and I guess I should use a USING clause to do that but I am not sure where to start with this expression.

ALTER TABLE myschema."MyTable"
ALTER COLUMN "MyColumn" TYPE uuid
USING ?

Could anyone show an example or some instruction docs?

CodePudding user response：

With no uuid-ossp module dependency:

ALTER TABLE myschema."MyTable"
ALTER COLUMN "MyColumn" TYPE uuid
using (
 case 
   when "MyColumn" = 0 then '00000000-0000-0000-0000-000000000000'::uuid
   else gen_random_uuid()
 end
);

This will generate UUID v4 and not v1.

CodePudding user response：

It looks like people find answer to their question right after they post it in StackOverflow.

A CASE ... THEN ... ELSE ... expression works for me.

ALTER TABLE myschema."MyTable"
ALTER COLUMN "MyColumn" TYPE uuid
USING CASE WHEN "MyColumn"=0 THEN uuid_nil() ELSE uuid_generate_v1() END

Although uuid_generate_v1() always generateds the same value but it can be fixed out of box later.