Peewee create an index with type casting

Question

I've looked around online and haven't been able to find a solution, but apologies if this question has been asked before.

I am trying to create an index on a jsonb column similar to the raw query:

CREATE INDEX idx ON table((jb_column::text));

where jb_column is the jsonb field.

I'm running into issues applying the index using the model class. I've tried mytable.add_index(SQL(CREATE INDEX idx ...)). While this does create the desired index, when I try to reset my database using the built-in model.delete().execute() functionality it fails to delete this index.

Therefore, I've also tried creating the index using more built-in peewee functionality. I have attempted:

idx = (mytable.index(
           mytable.jb_column.cast('text')))
mytable.add_index(idx)

However, this does not create the desired index. Any advice?

Thank you :)

Answer 1

The problem is that this is the SQL evaluated by default (note the collision of the index names):

class MyTbl(Model):
    data = BinaryJSONField()

MyTbl.add_index(MyTbl.index(MyTbl.data.cast('text')))
db.create_tables([MyTbl])

Produces:

CREATE TABLE IF NOT EXISTS "mytbl" (
    "id" SERIAL NOT NULL PRIMARY KEY, 
    "data" JSONB NOT NULL)
CREATE INDEX IF NOT EXISTS "mytbl_data" ON "mytbl" USING GIN ("data")
CREATE INDEX IF NOT EXISTS "mytbl_data" ON "mytbl" CAST("data" AS text))

Solution - specify a name on our custom index:

idx = MyTbl.index(MyTbl.data.cast('text'), name='mytbl_data_text')
MyTbl.add_index(idx)

Solution 2 - do not use the default GIN index:

class MyTbl(Model):
    data = BinaryJSONField(index=False)  # GIN index won't be created.

... add custom index here ...