Thanks for quick and comprehensive reply Michael.
I apologise for having been lazy and not providing an example that
illustrated the issue. The examples of rendered SQL were what I got from
the PgAdminIII inspection of the index (which I didn't even edit
correctly!), so I guess PostgreSQL 9.2 creates btree indexes by default if
the type of index is omitted.
I am trying to create the index in a 'mapper_configured' event listener of
a declarative model mixin (not sure if this is the right place, but it was
the only one I could find where I could get the information I needed, i.e.
the local table from the mapper of the model that included the mixin, using
'propagate=True' in the event listener), and for some weird reason, when
creating the index there, I get the behaviour I described, even when using
"postgresql_using='btree'".
Evidently when running your example, the index SQL is rendered as
advertised, so this will definitely require a better investigation of the
issue to try to understand what is happening in my case. When I find what
really is going on I'll come back.
Thanks again for your help.
--Pedro.
On Monday, 7 October 2013 19:06:47 UTC+1, Michael Bayer wrote:
>
> there's no "USING btree" generated by the CreateIndex construct unless you
> specify "postgresql_using" which I do not see in your code.
>
> here's a test using both desc() and "using" which works as advertised:
>
> from sqlalchemy import *
>
> t = Table('t', MetaData(), Column('column', String(50)))
>
> ix = Index('ix_table_column_desc', t.c.column.desc(),
> postgresql_using='btree')
>
> e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
>
> c = e.connect()
> tr = c.begin()
>
> t.create(c)
>
> output:
>
> 2013-10-07 14:05:28,064 INFO sqlalchemy.engine.base.Engine select version()
> 2013-10-07 14:05:28,064 INFO sqlalchemy.engine.base.Engine {}
> 2013-10-07 14:05:28,065 INFO sqlalchemy.engine.base.Engine select
> current_schema()
> 2013-10-07 14:05:28,065 INFO sqlalchemy.engine.base.Engine {}
> 2013-10-07 14:05:28,067 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
> 2013-10-07 14:05:28,067 INFO sqlalchemy.engine.base.Engine
> CREATE TABLE t (
> "column" VARCHAR(50)
> )
>
>
> 2013-10-07 14:05:28,067 INFO sqlalchemy.engine.base.Engine {}
> 2013-10-07 14:05:28,069 INFO sqlalchemy.engine.base.Engine CREATE INDEX
> ix_table_column_desc ON t USING btree ("column" DESC)
> 2013-10-07 14:05:28,069 INFO sqlalchemy.engine.base.Engine {}
>
>
>
>
>
>
>
>
>
>
> On Oct 7, 2013, at 1:37 PM, Pedro Romano <[email protected] <javascript:>>
> wrote:
>
> According to the
> documentation<http://docs.sqlalchemy.org/en/rel_0_8/core/constraints.html?highlight=desc#functional-indexes>,
>
> the following index definition:
>
> Index('ix_table_column_desc', table.c.column.desc())
>
> should render in PostgreSQL to:
>
> CREATE INDEX ix_table_column_desc
> ON table
> USING btree
> (modified DESC);
>
> but instead the DESC is omitted and I only get:
>
> CREATE INDEX ix_table_column_desc
> ON table
> USING btree
> (modified);
>
> Am I missing some detail here? Should I create a ticket about this issue?
>
> Thanks,
> --Pedro.
>
> --
> 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.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
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.
For more options, visit https://groups.google.com/groups/opt_out.
