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?
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+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.
