Re: [sqlalchemy] Filtering on Postgresql BigInteger array

[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

On Apr 16, 2013, at 12:42 PM, Alexander Bachmeier 
alexander.bachme...@gmail.com wrote:

 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.
 
 
 -- 
 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+unsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy@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.
  
  

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@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.

Re: [sqlalchemy] Filtering on Postgresql BigInteger array

[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. 

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@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.