Re: [sqlalchemy] Postgresql - Index on a json field
On 08/28/2016 07:53 AM, koch...@gmail.com 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
['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("").
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
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
,
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
Re: [sqlalchemy] Postgresql - Index on a json field
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
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['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("").
>
>
> 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
>>
>> 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
>> ,
>>
>> 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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Postgresql - Index on a json field
Great, thanks again for the replies and thanks for the awesome tool. On Saturday, July 5, 2014 1:28:34 PM UTC-7, Michael Bayer wrote: On 7/5/14, 3:14 PM, Phillip Aquilina wrote: 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 the reflection side, expressions inside of indexes (which is largely a Postgresql thing) are skipped at the moment. This doesn't present any issues. It only means if you're using Alembic to autogenerate migrations, you might have to type these expressions into your migration file manually. -- 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.
Re: [sqlalchemy] Postgresql - Index on a json field
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
Re: [sqlalchemy] Postgresql - Index on a json field
On 7/5/14, 3:14 PM, Phillip Aquilina wrote: 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 the reflection side, expressions inside of indexes (which is largely a Postgresql thing) are skipped at the moment. This doesn't present any issues. It only means if you're using Alembic to autogenerate migrations, you might have to type these expressions into your migration file manually. -- 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.
Re: [sqlalchemy] Postgresql - Index on a json field
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.
Re: [sqlalchemy] Postgresql - Index on a json field
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
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
sqlalchemy+...@googlegroups.com.
To post to this group, send email to
sqlal...@googlegroups.com.
Visit this group at
http://groups.google.com/group/sqlalchemy
http://groups.google.com/group/sqlalchemy.
For more options, visit
Re: [sqlalchemy] Postgresql - Index on a json field
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
Re: [sqlalchemy] Postgresql - Index on a json field
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 javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
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 javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
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.
[sqlalchemy] Postgresql - Index on a json field
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+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.
Re: [sqlalchemy] Postgresql - Index on a json field
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+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@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.
Re: [sqlalchemy] Postgresql - Index on a json field
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 javascript:. To post to this group, send email to sqlal...@googlegroups.com javascript:. 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.
Re: [sqlalchemy] Postgresql - Index on a json field
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 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 javascript:. To post to this group, send email to sqlal...@googlegroups.com 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 sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@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.
Re: [sqlalchemy] Postgresql - Index on a json field
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
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 javascript:.
To post to this group, send email to sqlal...@googlegroups.com
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 sqlalchemy+unsubscr...@googlegroups.com
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@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
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@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.
