Django / PostgreSQL varchar到UUID

编程入门行业动态更新时间:2024-10-28 05:25:17

本文介绍了Django / PostgreSQL varchar到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';

详细信息：