'SQL Alchemy GIN index on (UUID, JSONB)

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"
postgresqlsqlalchemy


Solution 1:[1]

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)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Edouard

Related Questions