"No operator matches the given name and argument" filtering on postgres array

[Matthew Pounsett]

I'm trying to work with the postgres ARRAY type and I'm having a hard time figuring out what I'm doing wrong with filtering queries on the array column.  Here's some sample code, omitting the session setup:

class MyTable(Base):                                                            

   __tablename__ = 'mytable'

   id = Column(Integer, primary_key=True)                                      

   myset = Column(sqlalchemy.dialects.postgresql.ARRAY(String))                                    

Base.metadata.create_all(engine)                                                

                                                                                

z = ['a', 'b', 'c']                                                                                                                                             

match = session.query(MyTable).\                                                

        filter(MyTable.myset == z).\                                            

        all()                                                                                                                                                   

Note that the table is completely empty at this point, and has just been created by the create_all() method.  I expect to get back an empty 'match' list, but when the query runs I get a long stack trace and the following sql error:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) operator does not exist: character varying[] = text[]

LINE 3: WHERE mytable.myset = ARRAY[E'a', E'b', E'c']

                            ^

HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

 'SELECT mytable.id AS mytable_id, mytable.myset AS mytable_myset \nFROM mytable \nWHERE mytable.myset = %(myset_1)s' {'myset_1': ['a', 'b', 'c']}

On the off chance it mattered, I've tried setting z to be a tuple instead of a list, but no joy there either.  What am I misunderstanding about how the array type works?

Thanks!

[Michael Bayer]

On Dec 10, 2013, at 12:00 AM, Matthew Pounsett <matt.p...@gmail.com> wrote:

> I'm trying to work with the postgres ARRAY type and I'm having a hard time figuring out what I'm doing wrong with filtering queries on the array column. Here's some sample code, omitting the session setup:
>
> class MyTable(Base):
> __tablename__ = 'mytable'
> id = Column(Integer, primary_key=True)
> myset = Column(sqlalchemy.dialects.postgresql.ARRAY(String))
>
> Base.metadata.create_all(engine)
>
> z = ['a', 'b', 'c']
> match = session.query(MyTable).\
> filter(MyTable.myset == z).\
> all()

that’s a little strange but you can get around it using CAST:

match = session.query(MyTable).\
filter(MyTable.myset == cast(z, ARRAY(String))).\
all()

though maybe there’s a better operator to use for array == array comparison, not sure


of course if you declare you column like this:

myset = Column(ARRAY(Text))


it works…..in Postgresql there’s no real difference between TEXT and VARCHAR (which is unusual). Not sure why PG can’t cast between array of VARCHAR vs. TEXT but I bet there’s some info on the web about that, it’s unusual PG would have a problem with something like that (though a google seems to confirm it).

[Matthew Pounsett]

On Tuesday, 10 December 2013 01:00:51 UTC-5, Michael Bayer wrote:

that’s a little strange but you can get around it using CAST:

match = session.query(MyTable).\
        filter(MyTable.myset == cast(z, ARRAY(String))).\
        all()

Unfortunately, that doesn't work.  

sqlalchemy.exc.ProgrammingError: (ProgrammingError) cannot cast type record to character varying[]

LINE 3: WHERE mytable.myset = CAST((E'a', E'b', E'c') AS VARCHAR[])

                              ^

 'SELECT mytable.id AS mytable_id, mytable.myset AS mytable_myset \nFROM mytable \nWHERE mytable.myset = CAST(%(param_1)s AS VARCHAR[])' {'param_1': ('a', 'b', 'c')}

of course if you declare you column like this:

   myset = Column(ARRAY(Text))

That doesn't change the original error.. it still complains:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) operator does not exist: character varying[] = text[]

LINE 3: WHERE mytable.myset = ARRAY[E'a', E'b', E'c']

                            ^

HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

 'SELECT mytable.id AS mytable_id, mytable.myset AS mytable_myset \nFROM mytable \nWHERE mytable.myset = %(myset_1)s' {'myset_1': ['a', 'b', 'c']}

So, I'm still looking for a solution..

Thanks for the suggestions.

[Matthew Pounsett]

On Tuesday, 10 December 2013 03:29:48 UTC-5, Matthew Pounsett wrote:

On Tuesday, 10 December 2013 01:00:51 UTC-5, Michael Bayer wrote:

that’s a little strange but you can get around it using CAST:

match = session.query(MyTable).\
        filter(MyTable.myset == cast(z, ARRAY(String))).\
        all()

Unfortunately, that doesn't work.  

In case this is a version issue, I should note I'm using 0.7.8, which is only a little bit behind what is currently shipping for FreeBSD (0.7.10).  Running under Python 2.7.3 

[Michael Bayer]

On Dec 10, 2013, at 3:29 AM, Matthew Pounsett <matt.p...@gmail.com> wrote:

On Tuesday, 10 December 2013 01:00:51 UTC-5, Michael Bayer wrote:

that’s a little strange but you can get around it using CAST:

match = session.query(MyTable).\
        filter(MyTable.myset == cast(z, ARRAY(String))).\
        all()

Unfortunately, that doesn't work.  

sqlalchemy.exc.ProgrammingError: (ProgrammingError) cannot cast type record to character varying[]

LINE 3: WHERE mytable.myset = CAST((E'a', E'b', E'c') AS VARCHAR[])

the test case I’m using is below.  You might want to make sure you’re on the latest psycopg2, this is also SQLAlchemy 0.8.4 but the SQL output seems the same.   Overall, if maybe you’re on an older postgresql version, you need to log in with psql, figure out what SQL query works directly, then just render that with whatever casts are needed.

from sqlalchemy import *

from sqlalchemy.orm import *

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.dialects.postgresql import ARRAY, array

Base = declarative_base()

class MyTable(Base):

   __tablename__ = 'mytable'

   id = Column(Integer, primary_key=True)

   myset = Column(ARRAY(String))

engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True)

Base.metadata.drop_all(engine)

Base.metadata.create_all(engine)

session = Session(engine)

z = ['a', 'b', 'c']

match = session.query(MyTable).\

        filter(MyTable.myset == cast(z, ARRAY(String))).\

        all()

                              ^

 'SELECT mytable.id AS mytable_id, mytable.myset AS mytable_myset \nFROM mytable \nWHERE mytable.myset = CAST(%(param_1)s AS VARCHAR[])' {'param_1': ('a', 'b', 'c')}

of course if you declare you column like this:

   myset = Column(ARRAY(Text))

That doesn't change the original error.. it still complains:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) operator does not exist: character varying[] = text[]

LINE 3: WHERE mytable.myset = ARRAY[E'a', E'b', E'c']

                            ^

HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

 'SELECT mytable.id AS mytable_id, mytable.myset AS mytable_myset \nFROM mytable \nWHERE mytable.myset = %(myset_1)s' {'myset_1': ['a', 'b', 'c']}

So, I'm still looking for a solution..

Thanks for the suggestions.

--
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.
For more options, visit https://groups.google.com/groups/opt_out.

[Matthew Pounsett]

On Tuesday, 10 December 2013 10:00:20 UTC-5, Michael Bayer wrote:

the test case I’m using is below.  You might want to make sure you’re on the latest psycopg2, this is also SQLAlchemy 0.8.4 but the SQL output seems the same.   Overall, if maybe you’re on an older postgresql version, you need to log in with psql, figure out what SQL query works directly, then just render that with whatever casts are needed.

Found the problem!  I think I forgot to drop the table at some point during my tests.  Your example below works, and if I go back to mine and make it match it now works too.  

I should be able to apply this to the actual object I was having trouble with, now.  Thanks for the help!