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?

Which version of postgres? It works fine in 9.3: alter table test alter val type int using (val::int); — Denis de Bernardy
– Denis de Bernardy, Commented Nov 29, 2013 at 11:31
9.1.10. I don't understand - the other column is the same table just was converted to integer without errors. But this column is still causing error. — FeroxTL
– FeroxTL, Commented Nov 29, 2013 at 11:37

Aditya Kresna Permana · Accepted Answer · 2021-08-24 08:32:53Z

14

Dealing with this problem you have to use 2 steps of migrations.

First: Add db_index=False on your first migration then generate and run the migration.

Second: Update db_index=True to your related column in the model (according to the first step) then generate the migration and run again.

This is based on my experience on some projects, and it works.

Hope it helps.

edited Aug 24, 2021 at 8:32

answered Apr 23, 2017 at 18:44

Aditya Kresna Permana

12.1k8 gold badges45 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rafael Gonçalves Over a year ago

Perfect, should be the accepted answer since it does not envolves SQL.

Aaron Lelevier Over a year ago

I tried this, but for my particular use case where a ForeignKey was a character varying in the PostgreSQL and not a uuid it didn't regen the correct migration files to update the column casting. I had to write pure SQL. I don't feel that great about it though, and am surprised. I would think that the migrations should never get into that state.

Denis de Bernardy · Accepted Answer · 2013-11-29 11:41:02Z

9

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

answered Nov 29, 2013 at 11:41

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related