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屋！