On 08/28/2016 07:53 AM, [email protected] wrote:
Hi Michael, I'm trying to adapt your answer above to declare indexes inside the __table_args__ tuple in my model class. However, when I run the alembic command to generate the migration script, the JSONB column indexes are not generated. I posted a SO question with more details, perhaps you could help me understand what I'm missing in the index generation http://stackoverflow.com/questions/39190935/jsonb-field-indexes-in-sqlalchemy-declarative-mappers
these are functional indexes which are not supported by Alembic's schema comparison feature right now. In fact you should see warning that states "autogenerate skipping functional index %s; not supported by SQLAlchemy reflection". Just write them out manually in your Alembic script.
Thanks in advance, Shailesh On Tuesday, 1 July 2014 10:53:13 UTC+5:30, Michael Bayer wrote: per the SO answer, you're looking for "CREATE INDEX ON publishers((info->>'name'));". Either you can emit this directly as a string, or use Index, just as it states: from sqlalchemy import create_engine, Integer, Index, Table, Column, MetaData from sqlalchemy.dialects.postgresql import JSON e = create_engine("postgresql://scott:tiger@localhost/test", echo=True) m = MetaData() publishers = Table('publishers', m, Column('id', Integer), Column('info', JSON)) Index("foo", publishers.c.info <http://publishers.c.info>['name'].astext) m.create_all(e) output: CREATE TABLE publishers ( id INTEGER, info JSON ) CREATE INDEX foo ON publishers ((info ->> 'name')) On 7/1/14, 1:14 AM, Mike Bayer wrote:I'm not familiar with any other style of index for this column type. If you can show me at http://www.postgresql.org/docs/9.4/static/datatype-json.html <http://www.postgresql.org/docs/9.4/static/datatype-json.html> or wherever what specific DDL you're looking for, you can simply emit it using engine.execute("<ddl>"). On 6/30/14, 11:02 PM, Phillip Aquilina wrote:Thanks for replying. I've read through that doc and I still don't see how that addresses my question. Is there somewhere in there that describes how to create an index on a json field? It seems like to me it's simple to create an index on a column but this would be creating an index on nested data inside the column. - Phil On Monday, June 30, 2014 6:07:51 PM UTC-7, Michael Bayer wrote: SQLAlchemy's API allows CREATE INDEX via the Index construct: http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html?highlight=index#indexes <http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html?highlight=index#indexes> On 6/30/14, 6:21 PM, Phillip Aquilina wrote:Using postgresql, I have a JSON type column. My understanding from their docs was that only jsonb columns could have an index created on them (a feature of postgresql 9.4) but then I found an SO answer <http://stackoverflow.com/questions/17807030/how-to-create-index-on-json-field-in-postgres-9-3> that said otherwise. I haven't had the chance to test it since I'm away from my dev environment, but the sqlalchemy docs seem to support this idea <http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html?highlight=json#sqlalchemy.dialects.postgresql.JSON>, mentioning the availability of "Index operations." Unless I'm missing something obvious (very possible), it seems like this can be done through sql, but is there a way to create an index on a json field through the sqlalchemy api? I can't seem to find a way to do this. Thanks, Phil -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy <http://groups.google.com/group/sqlalchemy>. For more options, visit https://groups.google.com/d/optout <https://groups.google.com/d/optout>.-- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <javascript:>. To post to this group, send email to [email protected] <javascript:>. Visit this group at http://groups.google.com/group/sqlalchemy <http://groups.google.com/group/sqlalchemy>. For more options, visit https://groups.google.com/d/optout <https://groups.google.com/d/optout>.-- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <javascript:>. To post to this group, send email to [email protected] <javascript:>. Visit this group at http://groups.google.com/group/sqlalchemy <http://groups.google.com/group/sqlalchemy>. For more options, visit https://groups.google.com/d/optout <https://groups.google.com/d/optout>.-- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <mailto:[email protected]>. To post to this group, send email to [email protected] <mailto:[email protected]>. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
-- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
