PostgreSQL将列类型从int更改为UUID [英] Postgresql change column type from int to UUID
问题描述
我想将列类型从 int
更改为 uuid
。我正在使用以下语句
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;
但是我收到错误消息
ERROR: column "colA" cannot be cast automatically to type uuid
HINT: Specify a USING expression to perform the conversion.
我很困惑如何使用 USING
推荐答案
您不能只将int4转换为uuid;它是无效的uuid,只能设置32位,高96位为零。
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.
如果要生成新的UUID来完全替换整数,并且如果不存在对这些整数的外键引用,则可以使用实际生成的伪强制转换新值。
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.
在没有备份数据的情况下,请勿运行此操作。它将永久丢弃 colA
中的旧值。
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());
更好的方法通常是添加一个uuid列,然后进行修复任何指向它的外键引用,最后都删除原始列。
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.
您需要安装UUID模块:
You need the UUID module installed:
CREATE EXTENSION "uuid-ossp";
引号很重要。
这篇关于PostgreSQL将列类型从int更改为UUID的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!