Django/PostgreSQL varchar to UUID

Question

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.

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;

I get this error:

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

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.

Answer 1

type uuid in your DDL statement is shorthand for SET DATA TYPE uuid. The manual:

SET DATA TYPE

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 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.

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.

@fl0cke pointed out a related answer:

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

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

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';

Details:

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

Answer 2

There may be another option available as of Postgresql 9.5. There is now a uuid operator class 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 has a fix I assume still works, but it is also pre-9.5: https://coderwall.com/p/1b5eyq/index-for-uuid-array-data-type

Note the blog specifically references use of uuid in an array, while the new operator class specifically references BRIN. Caveat Emptor.

Answer 3

I just had this issue and wanted to add info. In fixing the problem I wrapped my head around this via migrations. Anyone who can add to this, please feel free, corrections included.

This app is using Django 1.11, Py3, and was migrating away form early local dev on SQLite (just to proof of concept a few things). When migrating into PG I had the same error :

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

I was able to fix it 2 ways. In my early stage of the app I was able to wipe and start from scratch. I know this is rarely an option, but my actions provide helpful clues for fixing this in a migration or upgrade scenario. I have good SQL/PG chops coming form the time when ORM use was not common. The issue was a real head scratcher for me.

The Problem

In my 5 year old brain the issue came via changing column types, going from a string-ish to a UUID "native". Via the migrations in my application there seemed to be creation of a column that is not UUID native. With the introduction of Django's model UUIDField the demand was not able to be met and threw the aforementioned error. The issue is akin to doing something silly moving from str to int type in the DB with non-int values.

Fix Info

I was able to fix this 2 ways.

First was via Django's squash migration. I have already encountered a similar error related to UUID use in my app, so I was aware of this pattern of error. By squashing the migration you skip the earlier non-UUIDField column creation and only perform the correct column declaration. When I re-ran (fresh) migrate all went just fine.

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

So..

With all of that said, for me I was able to reverse engineer the issue in a way that works in my head. The "fix" was to never make a column other than the UUIDField friendly way. My issue was related to the SQLite to PG swap (AFIK).

If I was doing an upgrade I think the solutions of killing index(s) and the recreating them is the way to go.

Again, just trying to put a little info out there on this error, the googleweb was not returning anything that really caught me. So I got out the pickaxe and flash light.