Postgresql 将列类型从 int 更改为 UUID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20342717/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql change column type from int to UUID
提问by user1802143
I'd like to change the column type from an intto a uuid. I am using the following statement
我想将列类型从 an 更改int为 a uuid。我正在使用以下语句
ALTER TABLE tableA ALTER COLUMN colA SET DATA TYPE UUID;
But I get the error message
但我收到错误消息
ERROR: column "colA" cannot be cast automatically to type uuid
HINT: Specify a USING expression to perform the conversion.
I am confused how to use USINGto do the cast.
我很困惑如何USING用来做演员。
回答by Craig Ringer
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.
您不能只是将 int4 转换为 uuid；它会是一个无效的 uuid，只设置了 32 位，高 96 位为零。
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.
如果您想生成新的 UUID 以完全替换整数，并且如果没有对这些整数的现有外键引用，您可以使用实际生成新值的假转换。
Do not run thiswithout a backup of your data. It permanently throws away the old values in colA.
不要在没有备份数据的情况下运行它。它会永久丢弃 中的旧值colA。
ALTER TABLE tableA ALTER COLUMN colA SET DATA TYPE UUID USING (uuid_generate_v4());
A better approach is usually to adda 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:
您需要安装 UUID 模块：
CREATE EXTENSION "uuid-ossp";
The quotes are important.
引号很重要。
回答by pritstift
Just if someone comes across this old topic. I solved the problem by first altering the field into a CHAR type and then into UUID type.
只要有人遇到这个老话题。我首先将字段更改为 CHAR 类型，然后更改为 UUID 类型，从而解决了该问题。
回答by Michael Baltaks
I had to convert from text to uuid type, and from a Django migration, so after solving this I wrote it up at http://baltaks.com/2015/08/how-to-change-text-fields-to-a-real-uuid-type-for-django-and-postgresqlin case that helps anyone. The same techniques would work for an integer to uuid conversion.
我不得不从文本转换为 uuid 类型，并从 Django 迁移，所以在解决这个问题后，我在http://baltaks.com/2015/08/how-to-change-text-fields-to-a 上写了它-real-uuid-type-for-django-and-postgresql以防万一。相同的技术适用于整数到 uuid 的转换。
Based on a comment, I've added the full solution here:
根据评论，我在这里添加了完整的解决方案：
Django will most likely create a migration for you that looks something like:
Django 很可能会为您创建一个类似于以下内容的迁移：
class Migration(migrations.Migration):
dependencies = [
('app', '0001_auto'),
]
operations = [
migrations.AlterField(
model_name='modelname',
name='uuid',
field=models.UUIDField(db_index=True, unique=True),
),
]
First, put the auto created migration operations into a RunSQL operation as the state_operationsparameter. This allows you to provide a custom migration, but keep Django informed about what's happened to the database schema.
首先，将自动创建的迁移操作作为state_operations参数放入一个RunSQL 操作中。这允许您提供自定义迁移，但让 Django 了解数据库模式发生了什么。
class Migration(migrations.Migration):
dependencies = [
('app', '0001_auto'),
]
operations = [
migrations.RunSQL(sql_commands, None, [
migrations.AlterField(
model_name='modelname',
name='uuid',
field=models.UUIDField(db_index=True, unique=True),
),
]),
]
Now you'll need to provide some SQL commands for that sql_commandsvariable. I opted to put the sql into a separate file and then load in with the following python code:
现在您需要为该sql_commands变量提供一些 SQL 命令。我选择将 sql 放入一个单独的文件中，然后使用以下 python 代码加载：
sql_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), '0001.sql')
with open(sql_path, "r") as sqlfile:
sql_commands = sqlfile.read()
Now for the real tricky part, where we actually perform the migration. The basic command you want looks like:
现在是真正棘手的部分，我们实际执行迁移的地方。您想要的基本命令如下所示：
alter table tablename alter column uuid type uuid using uuid::uuid;
alter table tablename alter column uuid type uuid using uuid::uuid;
But the reason we are here is because of indexes. And as I discovered, Django likes to use your migrations to created randomly named indexes on your fields while running tests, so your tests will fail if you just delete and then recreate a fixed name index or two. So the following is sql that will delete one constraint and all indexes on the text field before converting to a uuid field. It also works for multiple tables in one go.
但我们在这里的原因是因为索引。正如我发现的那样，Django 喜欢在运行测试时使用您的迁移在您的字段上创建随机命名的索引，因此如果您只是删除然后重新创建一个或两个固定名称索引，您的测试将失败。所以下面的 sql 将在转换为 uuid 字段之前删除文本字段上的一个约束和所有索引。它还可以一次性处理多个表。
DO $$
DECLARE
table_names text[];
this_table_name text;
the_constraint_name text;
index_names record;
BEGIN
SELECT array['table1',
'table2'
]
INTO table_names;
FOREACH this_table_name IN array table_names
LOOP
RAISE notice 'migrating table %', this_table_name;
SELECT CONSTRAINT_NAME INTO the_constraint_name
FROM information_schema.constraint_column_usage
WHERE CONSTRAINT_SCHEMA = current_schema()
AND COLUMN_NAME IN ('uuid')
AND TABLE_NAME = this_table_name
GROUP BY CONSTRAINT_NAME
HAVING count(*) = 1;
if the_constraint_name is not NULL then
RAISE notice 'alter table % drop constraint %',
this_table_name,
the_constraint_name;
execute 'alter table ' || this_table_name
|| ' drop constraint ' || the_constraint_name;
end if;
FOR index_names IN
(SELECT i.relname AS index_name
FROM pg_class t,
pg_class i,
pg_index ix,
pg_attribute a
WHERE t.oid = ix.indrelid
AND i.oid = ix.indexrelid
AND a.attrelid = t.oid
AND a.attnum = any(ix.indkey)
AND t.relkind = 'r'
AND a.attname = 'uuid'
AND t.relname = this_table_name
ORDER BY t.relname,
i.relname)
LOOP
RAISE notice 'drop index %', quote_ident(index_names.index_name);
EXECUTE 'drop index ' || quote_ident(index_names.index_name);
END LOOP; -- index_names
RAISE notice 'alter table % alter column uuid type uuid using uuid::uuid;',
this_table_name;
execute 'alter table ' || quote_ident(this_table_name)
|| ' alter column uuid type uuid using uuid::uuid;';
RAISE notice 'CREATE UNIQUE INDEX %_uuid ON % (uuid);',
this_table_name, this_table_name;
execute 'create unique index ' || this_table_name || '_uuid on '
|| this_table_name || '(uuid);';
END LOOP; -- table_names
END;
$$
回答by JChrist
I'm bumping to this after a long time, but there is a way to convert your integer column to a UUID with some kind of backwards-compatibility, namely keeping a way to have a reference to your old values, rather than dropping your values. It comprises of converting your integer value to a hex string and then padding that with necesary zeroes to make up an artificial UUID.
很长一段时间后我遇到了这个问题，但是有一种方法可以将您的整数列转换为具有某种向后兼容性的 UUID，即保留一种引用旧值的方法，而不是删除您的值. 它包括将您的整数值转换为十六进制字符串，然后用必要的零填充它以组成一个人工 UUID。
So, assuming your current integer column is named ColA, the following statement would do it (mind the usingpart):
因此，假设您当前的整数列名为 ColA，则以下语句将执行此操作（请注意using部分）：
ALTER TABLE tableA ALTER COLUMN ColA SET DATA TYPE UUID USING LPAD(TO_HEX(ColA), 32, '0')::UUID;
回答by ajxs
I was able to convert a column with an INTtype, configured as an incrementing primary key using the SERIALshorthand, using the following process:
我能够使用以下过程转换具有INT类型的列，使用SERIAL速记配置为递增主键：
-- Ensure the UUID extension is installed.
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- Dropping and recreating the default column value is required because
-- the default INT value is not compatible with the new column type.
ALTER TABLE table_to_alter ALTER COLUMN table_id DROP DEFAULT,
ALTER COLUMN table_id SET DATA TYPE UUID USING (uuid_generate_v4()),
ALTER COLUMN table_id SET DEFAULT uuid_generate_v4();
回答by Michi Salazar
In PostgreSQL 9.3 you can do this:
在 PostgreSQL 9.3 中你可以这样做：
ALTER TABLE "tableA" ALTER COLUMN "ColA" SET DATA TYPE UUID USING "ColA"::UUID;
And cast the type of data to UUIDand this will avoid the error message.
并将数据类型转换为UUID，这将避免错误消息。
