SQL Alchemy GIN index on (UUID, JSONB)-CodePudding

I have a table with schema that has a json field. The filed has a lot of data populated to it already.

CREATE TABLE table(
id     UUID      NOT NULL
data    JSON       NOT NULL
);

I want to create an index for the json field. I have tried the script below

 __table_args__ = (
                  Index("index_table_on_data_gin",
                        "data",
                        postgresql_using='gin',),
               
                  ),
                   Index("index_table_on_id_gin",
                        "id",
                        postgresql_using='gin',),
               
                  ),
                  )

Here are the errors that I am getting.

JSON index creation error

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) data type json has no default operator class for access method "gin"

UUID index creation error

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) data type uuid has no default operator class for access method "gin"

CodePudding user response：

You can't index the json type, but you can index the jsonb type instead, see the manual. jsonb should be your preferred type (see the manual)

CodePudding user response：

You can use the btree_gin extension to index the UUID with GIN (though I don't see the point, why not just use regular btree for it?)