Change Postgres primary key type from varchar to uuid and it's referenced by other tables

Question

I have tables customer and user. I want to change their ids from character varying to uuid.

=>\d customers;
         Table "public.customers"
  Column  |       Type        | Modifiers
----------+-------------------+-----------
 id       | character varying | not null
 name     | character varying | not null
Indexes:
    "customers_pkey" PRIMARY KEY, btree (id)
    "customers_name_key" UNIQUE CONSTRAINT, btree (name)
Referenced by:
    TABLE "users" CONSTRAINT "users_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id) DEFERRABLE INITIALLY DEFERRED

=>\d users;
             Table "public.users"
    Column     |       Type        | Modifiers
---------------+-------------------+-----------
 id            | character varying | not null
 name          | character varying | not null
 customer_id   | character varying | not null
 login         | character varying | not null
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_login_key" UNIQUE CONSTRAINT, btree (login)
Foreign-key constraints:
    "users_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id) DEFERRABLE INITIALLY DEFERRED

I've tried:

=>begin;
set constraints all deferred;
ALTER TABLE users alter customer_id type uuid using customer_id::uuid;
ALTER TABLE customers alter id type uuid using id::uuid;
end;

But I got incompatible type error after this line

=> ALTER TABLE customers alter id type uuid using id::uuid;
ERROR:  foreign key constraint "users_customer_id_fkey" cannot be implemented
DETAIL:  Key columns "customer_id" and "id" are of incompatible types: character varying and uuid.

Answer 1

You would definitely want to drop the foreign key constraint. I was surprised that you didn't have to drop the PK constraint or index for PostgreSQL to change the data type like that. I set up a test and it worked fine, just had to drop the FK first and then re-add it afterward.