<p dir="auto"><strong>Migrated issue, originally created by Wichert Akkerman (<a href="https://github.com/wichert">@wichert</a>)</strong></p>
<p dir="auto">I need to build a trigram index for a set of columns in PostgreSQL. The SQL statement looks like this:</p>
<div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="CREATE INDEX trgm_idx ON my_table USING gist 
(language, (coalesce(col1, '') || ' ' || coalesce(col2, '')) gist_trgm_ops);"><pre class="notranslate"><span class="pl-k">CREATE</span> <span class="pl-k">INDEX</span> <span class="pl-en">trgm_idx</span> <span class="pl-k">ON</span> my_table USING gist 
(language, (coalesce(col1, <span class="pl-s"><span class="pl-pds">'</span><span class="pl-pds">'</span></span>) <span class="pl-k">||</span> <span class="pl-s"><span class="pl-pds">'</span> <span class="pl-pds">'</span></span> <span class="pl-k">||</span> coalesce(col2, <span class="pl-s"><span class="pl-pds">'</span><span class="pl-pds">'</span></span>)) gist_trgm_ops);</pre></div>
<p dir="auto">My attempt to do this using SQLAlchemy looks like this:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="schema.Index('trgm_idx', 
    Mytable.language,
    (func.coalesce(Mytable.col1, '') + ' ' + func.coalesce(MyTable.col2, '')).label('foo'),
    postgresql_ops={
        'foo': 'pg_trgm',
    },
    postgresql_using='gist')"><pre class="notranslate"><span class="pl-s1">schema</span>.<span class="pl-v">Index</span>(<span class="pl-s">'trgm_idx'</span>, 
    <span class="pl-v">Mytable</span>.<span class="pl-s1">language</span>,
    (<span class="pl-s1">func</span>.<span class="pl-en">coalesce</span>(<span class="pl-v">Mytable</span>.<span class="pl-s1">col1</span>, <span class="pl-s">''</span>) <span class="pl-c1">+</span> <span class="pl-s">' '</span> <span class="pl-c1">+</span> <span class="pl-s1">func</span>.<span class="pl-en">coalesce</span>(<span class="pl-v">MyTable</span>.<span class="pl-s1">col2</span>, <span class="pl-s">''</span>)).<span class="pl-en">label</span>(<span class="pl-s">'foo'</span>),
    <span class="pl-s1">postgresql_ops</span><span class="pl-c1">=</span>{
        <span class="pl-s">'foo'</span>: <span class="pl-s">'pg_trgm'</span>,
    },
    <span class="pl-s1">postgresql_using</span><span class="pl-c1">=</span><span class="pl-s">'gist'</span>)</pre></div>
<p dir="auto">I use <code class="notranslate">label()</code> just to get a key I can pass to <code class="notranslate">postgresql_ops</code>. Unfortunately this does not work. This is the generated SQL:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="CREATE INDEX trgm_idx ON my_table USING gist (language, (coalesce(col1, '') || ' ' || coalesce(col2, '')))"><pre class="notranslate"><code class="notranslate">CREATE INDEX trgm_idx ON my_table USING gist (language, (coalesce(col1, '') || ' ' || coalesce(col2, '')))
</code></pre></div>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/3432/issue3432.py">issue3432.py</a> | <a href="../wiki/imported_issue_attachments/3432/test.py">test.py</a></p>
<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto">this works in general, but fails on at least the Postgresql dialect because it is linking each expression to a column.  See <a href="https://bitbucket.org/zzzeek/alembic/issue/222/support-functional-indexes-with" rel="nofollow">https://bitbucket.org/zzzeek/alembic/issue/222/support-functional-indexes-with</a> for how alembic works around this.</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="from sqlalchemy import *
from sqlalchemy.schema import CreateIndex
from sqlalchemy.dialects import postgresql

m = MetaData()
t = Table('x', m)
idx = Index('foo', text(&quot;lower(c)&quot;))
idx._set_parent(t)

print CreateIndex(idx).compile(dialect=postgresql.dialect())"><pre class="notranslate"><code class="notranslate">from sqlalchemy import *
from sqlalchemy.schema import CreateIndex
from sqlalchemy.dialects import postgresql

m = MetaData()
t = Table('x', m)
idx = Index('foo', text("lower(c)"))
idx._set_parent(t)

print CreateIndex(idx).compile(dialect=postgresql.dialect())
</code></pre></div>
<p dir="auto">output:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="CREATE INDEX foo ON x ()"><pre class="notranslate"><code class="notranslate">CREATE INDEX foo ON x ()
</code></pre></div>
<p dir="auto">Index should be a lot more forgiving for indexes that have non-column expressions in it, and we should also provide for a "table" keyword.</p>