Postgresql array.contains doesn't accept set objects

[Former user]

(original reporter: taha_jahangir) When calling a query with .filter(my_col.contains({1, 2, 3}), (in sqlalchemy 0.8) The following error raises:

sqlalchemy.exc.StatementError: 'set' object does not support indexing (original cause: TypeError: 'set' object does not support indexing)

with traceback:
  File "build/bdist.freebsd-9.0-RELEASE-amd64/egg/sqlalchemy/orm/query.py", line 2049, in __getitem__
    return list(res)
  File "build/bdist.freebsd-9.0-RELEASE-amd64/egg/sqlalchemy/orm/query.py", line 2253, in __iter__
    return self._execute_and_instances(context)
  File "build/bdist.freebsd-9.0-RELEASE-amd64/egg/sqlalchemy/orm/query.py", line 2268, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "build/bdist.freebsd-9.0-RELEASE-amd64/egg/sqlalchemy/engine/base.py", line 664, in execute
    params)
  File "build/bdist.freebsd-9.0-RELEASE-amd64/egg/sqlalchemy/engine/base.py", line 764, in _execute_clauseelement
    compiled_sql, distilled_params
  File "build/bdist.freebsd-9.0-RELEASE-amd64/egg/sqlalchemy/engine/base.py", line 831, in _execute_context
    None, None)
  File "build/bdist.freebsd-9.0-RELEASE-amd64/egg/sqlalchemy/engine/base.py", line 1036, in _handle_dbapi_exception
    from e

This code was correct and running with sqlalchemy 0.7.

This is also true for generator objects, like {{{ .filter(my_col.contains(obj.id for obj in objects)) }}}

[Mike Bayer]

Replying to taha_jahangir:

This code was correct and running with sqlalchemy 0.7.

I don't see how that's possible - we had no support for "contains()" for ARRAY types in 0.7. Here's a test, feel free to show me your actual code (which is always very helpful, as I don't have to guess what you were doing, as I am having to do here...)

from sqlalchemy import *
from sqlalchemy.dialects.postgresql import ARRAY

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
t = Table('t', MetaData(), Column('x', ARRAY(Integer)))

conn = e.connect()
tr = conn.begin()

t.create(conn)
conn.execute(t.insert(), x=[2, 3](1,))

print conn.execute(t.select().where(t.c.x.contains({2, 3}))).fetchall()

as expected, "contains" in 0.7 was only for string types and we get this:

2013-03-12 11:37:41,462 INFO sqlalchemy.engine.base.Engine SELECT t.x 
FROM t 
WHERE t.x LIKE '%%' || %(x_1)s || '%%'
2013-03-12 11:37:41,462 INFO sqlalchemy.engine.base.Engine {'x_1': [3](2,)}
Traceback (most recent call last):
  File "test.py", line 13, in <module>
    print conn.execute(t.select().where(t.c.x.contains({2, 3}))).fetchall()
  File "/Users/classic/dev/sa07/lib/sqlalchemy/engine/base.py", line 1449, in execute
    params)
  File "/Users/classic/dev/sa07/lib/sqlalchemy/engine/base.py", line 1584, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/Users/classic/dev/sa07/lib/sqlalchemy/engine/base.py", line 1698, in _execute_context
    context)
  File "/Users/classic/dev/sa07/lib/sqlalchemy/engine/base.py", line 1691, in _execute_context
    context)
  File "/Users/classic/dev/sa07/lib/sqlalchemy/engine/default.py", line 331, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) operator is not unique: unknown || integer[3: WHERE t.x LIKE '%' || ARRAY[2, 3](]
LINE) || '%'
                           ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.
 "SELECT t.x \nFROM t \nWHERE t.x LIKE '%%' || %(x_1)s || '%%'" {'x_1': [3](2,)}

totally reasonable feature add of course but I'm not able to produce evidence that this is a regression without further information.

[Former user]

(original author: taha_jahangir) The code was column.op('@>')(set_or_list_object) in sqlalchemy 0.7

[Mike Bayer]

OK, sets/generators/whatever are supported, but you must specify a dimension for the ARRAY:

Table('mytable', metadata, Column('x', ARRAY(Integer, dimensions=1))

otherwise we have to guess how many dimensions are being sent, which requires looking into arr[0](0) which only works for list/tuple. You want to have a "dimension" specified always. The error message is now informative:

        "Cannot auto-coerce ARRAY value of type "
        "%s unless dimensions are specified "
        "for ARRAY type" % type(arr))

422f4718715ad89346f42caf03bdd2ed745e5a23

[Mike Bayer]

scratch that, I just coerce to a list() in all cases, so now it just works (at the expense of even more performance if you don't specify a dimension, so specify a dimension!)

d2f8c83e25ea7abb49315c30518f6415b497f1a7

[Mike Bayer]

I just broke empty arrays on that, fixed in 63c211f42730011760aa8e3f88b2171b23bc0a60.

[Mike Bayer]

Removing milestone: 0.8.xx (automated comment)