Django / PostgreSQL varchar到UUID [英] Django/PostgreSQL varchar to UUID

问题描述

我正在尝试将项目从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;

我收到此错误：

错误：运算符类 varchar_pattern_ops不接受数据类型uuid

ERROR: operator class "varchar_pattern_ops" does not accept data type 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相似的查询性能变化

• PostgreSQL LIKE query performance variations

请确保也修复所有此类查询。

Make sure to fix any such queries as well.

@ fl0cke指出一个相关的答案：

• Postgresql运算符类 varchar_pattern_ops不接受数据类型为整数

• 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?

我需要弄清楚如何检查现有索引。

I need to figure out how to examine the existent indices.

在现代版本的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';

详细信息：

• 将索引从一个表复制到另一个表


• 如何删除表的所有索引在Postgres中？

• Copy indexes from one table to another
• How can I drop all indexes of a table in Postgres?

这篇关于Django / PostgreSQL varchar到UUID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！