Friday, 6 September 2013

How to create a unique lowercase functional index using SQLAlchemy on PostgreSQL?

How to create a unique lowercase functional index using SQLAlchemy on
PostgreSQL?

This is the SQL I want to generate:
CREATE UNIQUE INDEX users_lower_email_key ON genders (LOWER(email));
From the SQLAlchemy Index documentation I would expect this to work:
Index('users_lower_email_key', func.lower(user.c.email), unique=True)
But after I call metadata.create(engine) the table is created but this
index is not. I've created a simplified example, using these other
examples as a base.
meta = MetaData()
mytable = Table('mytable', meta,
# an indexed column, with index "ix_mytable_col1"
Column('col1', Integer, index=True),
# a uniquely indexed column with index "ix_mytable_col2"
Column('col2', Integer, index=True, unique=True),
Column('col3', Integer),
Column('col4', Integer),
Column('col5', Integer),
Column('col6', Integer),
)
# place an index on col3, col4
Index('idx_col34', mytable.c.col3, mytable.c.col4)
# My attempt at a lowercase unique index
Index('banana123', func.lower(mytable.c.col5), unique=True)
Viewing the table definition in PostgreSQL I see that this index was not
created.
\d mytable
Table "public.mytable"
Column | Type | Modifiers
--------+---------+-----------
col1 | integer |
col2 | integer |
col3 | integer |
col4 | integer |
col5 | integer |
col6 | integer |
Indexes:
"ix_mytable_col2" UNIQUE, btree (col2)
"idx_col34" btree (col3, col4)
"ix_mytable_col1" btree (col1)
How can I create my lower, unique index?

No comments:

Post a Comment