I have a very simple table namely:
CREATE TABLE test(
id INT PRIMARY KEY DEFAULT unique_rowid()
)
And I want to change id type to bytes so that I can SET DEFAULT uuid_v4();.
However if I run this sql:
ALTER TABLE test MODIFY id bytes;
I'm getting an error:
pq: cannot convert INT8 to BYTES
Is there a possibility to alter table column from one type to another in cockroachdb?
Turned out, cockroach doesn't allow alter primary key constraint, so what I did was simply create new table with appropriate PK type, used INSERT INTO SELECT statement, deleted old table and then renamed new one.
They have it in official documentation:
A table's primary key can only be specified in the CREATE TABLE statement. It cannot be changed later using ALTER TABLE, though it is possible to go through a process to create a new table with the new primary key you want and then migrate the data.