Filtering on Postgresql BigInteger array

[Alexander Bachmeier]

I'm trying to query an array of bigint[] using SQLAlchemy 0.8 on a Postgresql database.

My query looks like this:

ways_with_node = db.query(Ways).filter(Ways.nodes.contains(array([node1.id], type_=BigInteger))).all()

and I'm getting the following exception:

cursor.execute(statement, parameters)

sqlalchemy.exc.ProgrammingError: (ProgrammingError) operator does not exist: bigint[] @> integer[]

LINE 3: WHERE ways.nodes @> ARRAY[ -710]

It looks like the array() function isn't changing the type to BigInteger. I've tried CHAR and the second argument remains an integer[] array.

The query should end up looking something like this:

select * from ways where nodes @> '{-710}'::bigint[]

Which successfully executes as plain SQL. I've tried different variations of the query, including:

db.query(Ways).filter(Ways.nodes.op('@>')(array([node1.id], type_=BigInteger))).all()

but the result is the same exception. Since I have run out of ideas, I was wondering if there is a bug in the array() function or if I'm doing something wrong here.

[Michael Bayer]

You need to use the cast() function here to do an explicit cast on the SQL side. array([cast(node.id, BigInteger)]).  The "type_" argument in most other cases only describes the type on the Python side. 

Sent from my iPhone

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Alexander Bachmeier]

On Tuesday, April 16, 2013 6:58:15 PM UTC+2, Michael Bayer wrote:

You need to use the cast() function here to do an explicit cast on the SQL side. array([cast(node.id, BigInteger)]).  The "type_" argument in most other cases only describes the type on the Python side. 

Thanks a lot, that fixed it for me. I was already working with the cast() before I found array and couldn't get it to work. I just never had the idea to combine those two.