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/
pgp2RVAZ6Mfbz.pgp
Description: PGP signature
