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

Question

This is the SQL I want to generate:

CREATE UNIQUE INDEX users_lower_email_key ON users (LOWER(email));

From the SQLAlchemy Index documentation I would expect this to work:

Index('users_lower_email_key', func.lower(users.c.email), unique=True)

But after I call metadata.create(engine) the table is created but this index is not. I've tried:

from conf import dsn, DEBUG

engine = create_engine(dsn.engine_info())

metadata = MetaData()
metadata.bind = engine

users = Table('users', metadata,
    Column('user_id', Integer, primary_key=True),
    Column('email', String),
    Column('first_name', String, nullable=False),
    Column('last_name', String, nullable=False),
    )

Index('users_lower_email_key', func.lower(users.c.email), unique=True)

metadata.create_all(engine)

Viewing the table definition in PostgreSQL I see that this index was not created.

\d users
                                   Table "public.users"
   Column   |       Type        |                        Modifiers
------------+-------------------+---------------------------------------------------------
 user_id    | integer           | not null default nextval('users_user_id_seq'::regclass)
 email      | character varying |
 first_name | character varying | not null
 last_name  | character varying | not null
Indexes:
    "users_pkey" PRIMARY KEY, btree (user_id)

How can I create my lower, unique index?

Why are you trying to take the lowercase of an INTEGER column. — SingleNegationElimination
– SingleNegationElimination, Commented Sep 6, 2013 at 21:00
@TokenMacGuy I was about to ask the same thing because that does not work and Postgres with throw an error. However, if you use a String type, then the example works perfectly and without a hassle. — javex
– javex, Commented Sep 6, 2013 at 21:02
My mistake; I didn't notice that my example was trying to lowercase an Integer. I've updated the example to be my actual table, where the issue also occurs. — skyler
– skyler, Commented Sep 6, 2013 at 21:16
can you show all of your code? Also, what version of SQLAlchemy are you using? What version of PostgreSQL? As mentioned in my answer, I have had no trouble creating the index using your code. — SingleNegationElimination
– SingleNegationElimination, Commented Sep 8, 2013 at 1:13

SingleNegationElimination · Accepted Answer · 2013-09-06 21:05:01Z

I have no idea why you want to index an integer column in lower case; The problem is that the generated sql does not typecheck:

LINE 1: CREATE UNIQUE INDEX banana123 ON mytable (lower(col5))
                                                  ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
 'CREATE UNIQUE INDEX banana123 ON mytable (lower(col5))' {}

On the other hand, if you use an actual string type:

Column('col5string', String),
...
Index('banana123', func.lower(mytable.c.col5string), unique=True)

The index is created as expected. If, for some very strange reason, you are insistent about this absurd index, you just need to fix the types:

Index('lowercasedigits', func.lower(cast(mytable.c.col5, String)), unique=True)

Which produces perfectly nice:

CREATE UNIQUE INDEX lowercasedigits ON mytable (lower(CAST(col5 AS VARCHAR)))

I didn't realize that my example was trying to lowercase an integer. I updated my example to use a string, where the index also fails to be created.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related