Django / PostgreSQL varchar for UUID

Question

I am trying to upgrade a project from Django 1.7 to 1.9. Unfortunately, he used the django-extensions UUIDfield , which used varchar internally. I am trying to change these fields to the uuid type in the database.

I already created custom migration, told Django that migration will use native SQL for this. My problem arises when I do this (the column is called guid ):

 alter table tablename alter column guid type uuid using guid::uuid; 

I get this error:

ERROR: operator class "varchar_pattern_ops" does not accept uuid data type

I'm really not so familiar with PostgreSQL and a little in front of me. Can I create a CAST or fix something? I can’t understand how I will be.

I am trying to use a script here which should take care of the indexes, but I am really above my head.

Answer 1

type uuid in your DDL statement is short for SET DATA TYPE uuid . Guide:

SET DATA TYPE

This form changes the type of the table column. Indexes and simple table constraints associated with a column will be automatically converted to use the new column type by reprocessing the original sentence. [...]

varchar_pattern_ops is the operator class that will be indicated in your error message if you have uuid using this operator class in any index. Generally, to speed up sorting, matching patterns and range conditions.

To fix, remove conflicting indexes, change the data type, and then recreate indexes without a special operator class - if you still need them.

However, some typical queries that use the varchar_pattern_ops index will stop working with the uuid data type instead of varchar . Similar to pattern matching:

• PostgreSQL LIKE query performance changes

Be sure to fix any such requests.

@ fl0cke pointed out the answer:

• Postgresql operator class "varchar_pattern_ops" does not accept data type integer

I suggest a slightly different route. It is cheaper to reset the index, change the data type, and then create a new index - if it is 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 an abusive index?

I need to figure out how to examine existing indexes.

In modern versions of Postgres, you get existing indexes for a table with \d tbl in psql.

To get all the complete CREATE INDEX statements for a given table:

 SELECT pg_get_indexdef(indexrelid) || ';' AS idx FROM pg_index WHERE indrelid = 'public.tbl'::regclass; -- optionally schema-qualified 

To get only those that use 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'; 

More details:

• Copy indexes from one table to another
• How can I delete all table indexes in Postgres?

Answer 2

I just had this problem and wanted to add information. Having fixed the problem, I wrapped it through migrations. Anyone who can add to this, please feel free to fix it.

This application uses Django 1.11, Py3, and it ported the form of an early local developer to SQLite (just a few things to prove the concept). When switching to PG, I had the same error:

ERROR: operator class "varchar_pattern_ops" does not accept uuid data type

I was able to fix this in two ways. At an early stage of the application, I was able to erase and start from scratch. I know this is rarely an option, but my actions provide useful hints to fix this in a migration or upgrade scenario. I have good SQL / PG chops that form a time when using ORM was not common. The question was a real puzzle for me.

Problem

In my 5 year old brain, a problem arose by changing the column types, moving from string-ish to UUID "native". Using migrations in my application, it seems like a column was created that is not a UUID native. With the introduction of the Django UUIDField model, demand was not satisfied and threw the above error. This problem is akin to the fact that you are doing something stupid moving from str to int type in a DB with values ​​other than int.

Error Information

I was able to fix this in two ways.

First, Django squash migration was done. I have already encountered a similar error related to the use of UUIDs in my application, so I knew about this error pattern. By resetting the migration, you skip earlier column creation without a UUIDField and only properly declare the column. When I started (fresh) migrate again, everything went fine.

The second method is hardly a variation, but I killed all the migrations and started from the current state. The same effect as the previous one.

So..

With all that said, for me, I was able to rearrange the problem in a way that works in my head. The "fix" was to never create a column other than UUIDField. My problem was with replacing SQLite with PG (AFIK).

If I were doing an update, I think that deciding to kill the index (s) and recreate them is the way to go.

Again, just trying to provide some information about this error, googleweb did not return anything that really caught me. So I chose a pick and a flash.

Answer 3

Postgresql 9.5 might be another option. Now there is a uuid statement operator https://www.postgresql.org/docs/9.5/static/brin-builtin-opclasses.html . Of course, I still have 9.4 :-( This blog post helps explain the problem and fixed it, I assume it still works, but it is also pre-9.5: https://coderwall.com/p/1b5eyq/index- for-uuid-array-data-type

Note that the blog specifically uses uuid in an array, while the new operator class specifies BRIN. Caveat Emptor.