我想将列类型从int
a 更改为a uuid
.我使用以下声明
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
用来做演员表.
就算有人碰到这个老话题。我先将字段更改为CHAR类型,然后更改为UUID类型,从而解决了该问题。
你不能只是将int4转换成uuid; 它是一个无效的uuid,只设置32位,高96位为零.
如果要生成新的UUID以完全替换整数,并且如果没有对这些整数的现有外键引用,则可以使用实际生成新值的伪转换.
如果没有备份数据,请不要运行此操作.它永久地抛弃旧的价值观colA
.
ALTER TABLE tableA ALTER COLUMN colA SET DATA TYPE UUID USING (uuid_generate_v4());
更好的方法通常是添加一个uuid列,然后修复任何指向它的外键引用,最后删除原始列.
您需要安装UUID模块:
CREATE EXTENSION "uuid-ossp";
报价很重要.
我必须从文本转换为uuid类型,并从Django迁移转换,因此在解决此问题后,我将其写在http://baltaks.com/2015/08/how-to-change-text-fields-to-a为Django和Postgresql设置真正的uuid类型,以防万一。相同的技术适用于整数到uuid的转换。
根据评论,我在此处添加了完整的解决方案:
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), ), ]
首先,将自动创建的迁移操作作为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), ), ]), ]
现在,您需要为该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()
现在,对于真正棘手的部分,我们将实际执行迁移。您想要的基本命令如下所示:
alter table tablename alter column uuid type uuid using uuid::uuid;
但是我们在这里的原因是因为索引。正如我发现的那样,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; $$