Replace integer id field with uuid

Question

I need to replace the default integer id in my model with an uuid. The problem is that it's beeing used in another model (foreignkey). Any idea on how to perform this operation without losing data?

class A(Base):
    __tablename__ = 'a'

    b_id = Column(
       GUID(), ForeignKey('b.id'), nullable=False,
       server_default=text("uuid_generate_v4()")
    )

class B(Base):
    __tablename__ = 'b'
    id = Column(
        GUID(), primary_key=True,
        server_default=text("uuid_generate_v4()")
    )

Unfortunately it doesn't work, also I'm afraid I'll break the relation.

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) default for column "id" cannot be cast automatically to type uuid

Alembic migration I've tried looks similar to:

op.execute('ALTER TABLE a ALTER COLUMN b_id SET DATA TYPE UUID USING (uuid_generate_v4())')

Answer 1

Add an id_tmp column to b with autogenerated UUID values, and a b_id_tmp column to a. Update a joining b on the foreign key to fill a.b_id_tmp with the corresponding UUIDs. Then drop a.b_id and b.id, rename the added columns, and reestablish the primary key and foreign key.

CREATE TABLE a(id int PRIMARY KEY, b_id int);
CREATE TABLE b(id int PRIMARY KEY);

ALTER TABLE a ADD CONSTRAINT a_b_id_fkey FOREIGN KEY(b_id) REFERENCES b(id);

INSERT INTO b VALUES (1), (2), (3);
INSERT INTO a VALUES (1, 1), (2, 2), (3, 2);

ALTER TABLE b ADD COLUMN id_tmp UUID NOT NULL DEFAULT uuid_generate_v1mc();
ALTER TABLE a ADD COLUMN b_id_tmp UUID;

UPDATE a SET b_id_tmp = b.id_tmp FROM b WHERE b.id = a.b_id;

ALTER TABLE a DROP COLUMN b_id;
ALTER TABLE a RENAME COLUMN b_id_tmp TO b_id;
ALTER TABLE b DROP COLUMN id;
ALTER TABLE b RENAME COLUMN id_tmp TO id;
ALTER TABLE b ADD PRIMARY KEY (id);
ALTER TABLE a ADD CONSTRAINT b_id_fkey FOREIGN KEY(b_id) REFERENCES b(id);

Just as an aside, it's more efficient to index v1 UUIDs than v4 since they contain some reproducible information, which you'll notice if you generate several in a row. That's a minor savings unless you need the higher randomness for external security reasons.