Flask-SQLAlchemy: Create functional index

Question

I'm trying to create a functional index (with the PostgreSQL function lower()). I get this error when using Alembic to create the table:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) column "lower" does not exist
 'CREATE INDEX ix_cities_name ON cities (lower)' {}

Here's the code:

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
db = SQLAlchemy(app)

class City(db.Model):
    __tablename__ = 'cities'

    name = db.Column(db.String(100), nullable=False)

db.Index('ix_cities_name', db.func.lower(db.metadata.tables['cities'].c.name))

According to docs.sqlalchemy.org/en/rel_0_8/core/… it should work, but I get the above error. — user3362454
– user3362454, Commented Feb 28, 2014 at 15:44

Wan Yang · Accepted Answer · 2015-02-09 03:57:42Z

3

You can do like this:

from sqlalchemy import func

db.Index('ix_cities_name', func.lower(City.name))

reference to:

Functional indexes are supported as well, typically by using the
:data:`.func` construct in conjunction with table-bound
:class:`.Column` objects::

    Index("some_index", func.lower(sometable.c.name))

In addition, thank you for this question, I know how to create the index!

answered Feb 9, 2015 at 3:57

Wan Yang

313 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alexey · Accepted Answer · 2014-04-14 11:40:09Z

1

Unfortunately alembic can't generate correct revision for functional indexes yet (I hope). You migration should be looks like:

op.execute("CREATE INDEX ix_cities_name ON cities (LOWER(name))")

answered Apr 14, 2014 at 11:40

Alexey

83213 silver badges22 bronze badges

Collectives™ on Stack Overflow

Flask-SQLAlchemy: Create functional index

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related