Thanks for the reply. Your example made me realize my model did not also use VARCHAR. Changing the model column type to match the casting type made this work.
On Friday, May 10, 2013 3:57:36 PM UTC-4, Audrius Kažukauskas wrote: > > On Mon, 2013-05-06 at 11:04:57 -0700, Glenn Yonemitsu wrote: > > In Postgresql I have a CMS entry model with a tag column varchar(20)[]. > I > > want to do a query so a row with any of the tags will be returned. I > know > > overlap is the method to use but I can't get the casting done correctly. > > > > Right now I am trying (and a lot of searching revealed similar > solutions): > > > > query.filter(ContentEntry.tag.overlap(cast(tags, ARRAY(VARCHAR(20))))) > > > > But I am getting the error: AttributeError: 'SQLCompiler' object has no > > attribute 'visit_array'. Any variation involving ARRAY() or array() says > > there is no attribute visit_ARRAY or visit_array. > > > > The following hand crafted SQL works, so for now I'm trying to at least > get > > to this point (and as I understand, overlap will use the && operator): > > > > SELECT content_entry.title, content_entry.tag FROM content_entry WHERE > > content_entry.tag && cast(array['foo', 'bar'] as varchar(20)[]); > > > > How can I get this casted correctly? > > This is strange, casting to varchar(20)[] works here without problems. > Given the following model: > > class Test(Base): > __tablename__ = 'test_arr' > id = Column(Integer, primary_key=True) > data = Column(ARRAY(VARCHAR(20))) > > The query > > session.query(Test).\ > filter(Test.data.overlap(cast(['foo'], ARRAY(VARCHAR(20))))) > > is compiled to > > SELECT test_arr.id AS test_arr_id, test_arr.data AS test_arr_data > FROM test_arr > WHERE test_arr.data && CAST(%(param_1)s AS VARCHAR(20)[]) > > I'm using PostgreSQL 9.2.4, psycopg2 2.5 and SQLAlchemy 0.8.1, though > somehow I doubt it has anything to do with different version numbers of > any of those. > > On a side note, if I were you, I wouldn't bother with varchar(20)[] and > use text[] instead. Internally varchar and text are stored the same > way, the only difference is the former sets the length constraint if it > is provided. > > -- > Audrius Kažukauskas > http://neutrino.lt/ > -- 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.
