postgres_where does not quote literal strings in predicate expression

[Former user]

In code like this:

Index('ix_foo',
      sometable.c.somecol,
      postgres_where=sometable.c.type=='somevalue')

I expect the index creation DDL to be:

CREATE UNIQUE INDEX ix_foo ON sometable (somecol) WHERE sometable.type = 'somevalue'

but it is:

CREATE UNIQUE INDEX ix_foo ON sometable (somecol) WHERE sometable.type = somevalue

So the string literal is not quoted, as it is when I specify WHERE conditions in queries. This violates the principle of least astonishment.

As a workaround, nested quoting ("'base'") works.

[Mike Bayer]

I'm fairly certain this was fixed in f9cb6f5834fb1acf4460fd9bb6b72f8c76f8c36c. A test that is specific towards strings should be added to test_postgresql.py. This is targeted at 0.6 only since backwards-compatible "guessing" for whether or not quotes were applied in 0.5 is not really worth it.

[Former user]

Unit test

[Former user]

It works with 9cdbf8e8fe0c5b01ca9e38aea49e6a05079402f0 at least.

Attached a unit test.

– AlexB

[Former user]

However, the current approach will fail if the string contains '-characters, such as

        idx = Index('test_idx1', tbl.c.data, postgresql_where=and_(tbl.c.data > 'a', tbl.c.data < "b's"))

It results in CREATE INDEX test_idx1 ON testtbl (data) WHERE data > 'a' AND data < "b's", which is wrong because the latter is quoted as an identifier.

[Mike Bayer]

this is why we never wanted to get into this. we essentially have to duplicate the quoting capability of the DBAPI and/or client library. oh well

[Mike Bayer]

d66f470326c59330e323ad470d6014d2adbba729

[Mike Bayer]

Removing milestone: 0.6.0 (automated comment)