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?