问题描述
我正在尝试将项目从Django 1.7更新到1.9。不幸的是，它使用了django扩展名 UUIDfield ，该扩展名在内部使用了 varchar 。我正在尝试将这些字段更改为数据库中的 uuid 类型。
I'm trying to update a project from Django 1.7 to 1.9. It unfortunately used the django-extensions UUIDfield which used a varchar internally. I'm trying to change those fields to the uuid type in the database.
我已经创建了一个自定义迁移，告诉Django迁移将使用自己的SQL进行。我的问题是当我这样做时（列名为 guid ）：
I've already created a custom migration, told Django the migration is going to use its own SQL to do it. My problem comes when I do this (the column is named guid):
alter table tablename alter column guid type uuid using guid::uuid;
我收到此错误：
我对PostgreSQL确实不那么熟悉有点在我头上我可以创建一个CAST或其他解决方法吗？我不知道该怎么办。
I am really not that familiar with PostgreSQL and am in a bit over my head. Can I create a CAST or something to fix this? I can't figure out how I would.
我正在尝试使用，该脚本应该处理索引依赖性，但是我
I am trying to use the script from here which is supposed to take care of index dependencies but I am really in over my head.
推荐答案
键入uuid 语句是 SET DATA TYPE uuid 的简写。
此表单更改了表格的一列。通过重新解析最初提供的表达式，涉及该列的索引和简单表约束将自动转换为使用新的列类型。 [...]
This form changes the type of a column of a table. Indexes and simple table constraints involving the column will be automatically converted to use the new column type by reparsing the originally supplied expression. [...]
varchar_pattern_ops 是您有 uuid 在任何索引中使用此运算符类。通常可以实现更快的排序，模式匹配和范围条件。
varchar_pattern_ops is an operator class that would be mentioned in your error message if you have uuid using this operator class in any index. Typically to enable faster sorting, pattern matching and range conditions.
要修复，删除有冲突的索引，更改数据类型，然后在没有特殊运算符类的情况下重新创建索引- 如果您仍然需要它们。
To fix, drop conflicting indexes, alter the data type and then re-create indexes without the special operator class - if you still need them.
但是，一些典型的查询会使用 varchar_pattern_ops 索引将停止使用数据类型 uuid 而不是 varchar 。像模式匹配一样：
However, some typical queries that would make use of a varchar_pattern_ops index would stop working with data type uuid instead of varchar. Like pattern-matching:
- PostgreSQL LIKE query performance variations
请确保也修复所有此类查询。
Make sure to fix any such queries as well.
一个相关的答案：
- Postgresql operator class "varchar_pattern_ops" does not accept data type integer
我建议使用略有不同的路由。如果仍然有用，则删除索引，更改数据类型并然后创建新索引会比较便宜。
I suggest a slightly different route. It's cheaper to drop the index, change the data type and then create a new index - if it's still useful.
DROP INDEX tbl_guid_varchar_pattern_ops_idx;
ALTER TABLE tbl ALTER COLUMN guid TYPE uuid USING guid::uuid;
CREATE INDEX tbl_guid_idx ON tbl (guid);
如何查找违规索引？
How to find offending index?
在现代版本的Postgres中，您已经存在在psql中具有 bd tbl 的表的索引。
In modern versions of Postgres you get existing indexes for the table with \d tbl in psql.
要获取所有完整的为给定表创建索引语句：
To get all complete CREATE INDEX statements for the given table:
SELECT pg_get_indexdef(indexrelid) || ';' AS idx
FROM pg_index
WHERE indrelid = 'public.tbl'::regclass; -- optionally schema-qualified
仅使用 varchar_pattern_ops
To get just the ones using varchar_pattern_ops:
SELECT pg_get_indexdef(i.indexrelid) || ';' AS idx
FROM pg_index i
JOIN pg_opclass o ON o.oid = ANY (i.indclass)
WHERE i.indrelid = 'public.big'::regclass
AND o.opcname = 'varchar_pattern_ops';
详细信息：
- Copy indexes from one table to another
- How can I drop all indexes of a table in Postgres?
这篇关于Django / PostgreSQL varchar到UUID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！