Postgresql change column type from int to UUID

Question

I'd like to change the column type from an int to a uuid. I am using the following statement

ALTER TABLE tableA ALTER COLUMN colA SET DATA TYPE UUID; 

But I get the error message

ERROR:  column "colA" cannot be cast automatically to type uuid HINT:  Specify a USING expression to perform the conversion. 

I am confused how to use USING to do the cast.

Answer 1

You can't just cast an int4 to uuid; it'd be an invalid uuid, with only 32 bits set, the high 96 bits being zero.

If you want to generate new UUIDs to replace the integers entirely, and if there are no existing foreign key references to those integers, you can use a fake cast that actually generates new values.

Do not run this without a backup of your data. It permanently throws away the old values in colA.

ALTER TABLE tableA ALTER COLUMN colA SET DATA TYPE UUID USING (uuid_generate_v4()); 

A better approach is usually to add a uuid column, then fix up any foreign key references to point to it, and finally drop the original column.

You need the UUID module installed:

CREATE EXTENSION "uuid-ossp"; 

The quotes are important.