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

Question

I'm trying migrate my data id postgresql from string to integers in django to use them in sphinx search. So first of all I'm making data migration, converting my data to integers in string like this

db.execute('''UPDATE the_table SET foo='1' WHERE foo='bar';''')

Then I'm making schema migration

ALTER TABLE the_table ALTER COLUMN col_name TYPE integer USING (col_name::integer);

like it was told here

But I'm getting an error

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

SQL-состояние: 42804

This error occurs both in South and pgAdmin. The data is correct - it is Null or integer in string type. What am I doing wrong?

Answer 1

I'm only able to reproduce your error message like so:

denis=# create index test_idx on test (val varchar_pattern_ops);
CREATE INDEX
denis=# alter table test alter val type int using (val::int);
ERROR:  operator class "varchar_pattern_ops" does not accept data type integer

If you've a funky index like that, try dropping and recreating it like so:

denis=# drop index test_idx;
DROP INDEX
denis=# create index test_idx on test (val);
CREATE INDEX
denis=# alter table test alter val type int using (val::int);
ALTER TABLE

Related docs:

http://www.postgresql.org/docs/current/static/indexes-opclass.html