I finally had a chance to try this with the inspector and quickly discovered it doesn't support expression-based indexes with this warning, "Skipped unsupported reflection of expression-based index <some_index>." I can patch this out locally for my own needs but is there a bigger reason this is not yet supported that I should be worried about?
On Wednesday, July 2, 2014 10:35:02 AM UTC-7, Phillip Aquilina wrote: > > Perfect thanks Mike. > > On Wednesday, July 2, 2014 10:17:17 AM UTC-7, Michael Bayer wrote: >> >> >> On 7/2/14, 11:38 AM, Phillip Aquilina wrote: >> >> This worked as described. Thanks again. I have a followup question. It >> doesn't seem like there's an analog to table.create(checkfirst=True) for an >> Index. I found this issue >> <https://bitbucket.org/zzzeek/sqlalchemy/issue/527/indexcreate-should-take-checkfirst> >> >> that seems to mention having this functionality but it doesn't look like >> it's been implemented? Is there a normal workaround for this? >> >> >> typically Index is bundled with its parent Table, and the conditional >> aspect of it comes from the Table being created conditionally. >> Otherwise, if the Index is added after the fact, typically people are using >> migration tools to get that so that's where the conditional aspect comes >> in. So the case where Index.create() really needs conditional behavior is >> slim. You can for now use inspector: >> >> from sqlalchemy import inspect >> insp = inspect(engine) >> for idx in insp.get_indexes('tablename'): >> if idx['name'] == 'myname': >> break >> else: >> Index('myname', x, y, z).create(engine) >> >> >> >> >> >> On Tuesday, July 1, 2014 10:03:40 AM UTC-7, Phillip Aquilina wrote: >>> >>> Ah! I'll give that a try. Thanks Mike. >>> >>> On Monday, June 30, 2014 10:23:13 PM UTC-7, 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['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 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 >>>>> >>>>> >>>>> 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 sqlalchemy+...@googlegroups.com. >>>>> To post to this group, send email to sqlal...@googlegroups.com. >>>>> Visit this group at http://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 sqlalchemy+...@googlegroups.com. >>>> To post to this group, send email to sqlal...@googlegroups.com. >>>> Visit this group at http://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 sqlalchemy+...@googlegroups.com. >>>> To post to this group, send email to sqlal...@googlegroups.com. >>>> Visit this group at http://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 sqlalchemy+...@googlegroups.com. >> To post to this group, send email to sqlal...@googlegroups.com. >> Visit this group at http://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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
