Changing the data type of id column

August 11, 2023

I’m trying to update the data type of the id column from public.profiles table within supabase. i’ve been running this command:

ALTER TABLE public.profiles
ALTER COLUMN id type uuid_generate_v4();

then run supabase db reset to see the changes in local db but send me back an error

Error: ERROR: identity column type must be smallint, integer, or bigint (SQLSTATE 22023)
At statement 0: ALTER TABLE public.profiles       
ALTER COLUMN id set data type uuid

In local db with supabase i have activated the extension uuid-ossp

>Solution :

You must first drop the IDENTITY attribute from the column.
Then change the data type to uuid, providing a translation expression from int -> uuid. I chose md5(id::text)::uuid. See:

What is the optimal data type for an MD5 field?

Then set the default to uuid_generate_v4() – the additional module "uuid-ossp" must be installed for that, obviously.

DROP IDENTITY must go first. The rest can be done in a single command. Best do it all in a single transaction.

ALTER TABLE profiles ALTER COLUMN id DROP IDENTITY IF EXISTS;

ALTER TABLE profiles
  ALTER COLUMN id type uuid USING md5(id::text)::uuid
, ALTER COLUMN id SET DEFAULT uuid_generate_v4();

fiddle

Rewrites the whole table. So you may want to run VACUUM FULL ANALYZE to remove bloat afterwards (and clean up other stuff).

If id is also the PK, that’s adapted automatically.
Alternatively, drop the column id and add a new one. Would be cheaper for big tables. The new column is appended at the end of the table, though.

Or create a copy of the table, switching out the id column in the process. If you can afford that.