Querying postgres array ?

[Nicolas Steinmetz]

Hello,

Using SQLAlchemy 0.7.9 and Flask-SQLAlchhemy 0.16, I want to use Postgres array.

So my model is as follow :

from sqlalchemy.dialects.postgresql import ARRAY

class Mark(db.Model):

    __tablename__ = 'mark'

    id = db.Column(db.Integer, primary_key=True)

    title = db.Column(db.String(200), unique=True)

    url = db.Column(db.String(200), unique=True)

    description = db.Column(db.Text())

    comment = db.Column(db.Text())

    tags = db.Column(ARRAY(db.String))

    author = db.relationship('User', backref=db.backref('user', lazy='joined'))

    created_at = db.Column(db.DateTime)

    updated_at = db.Column(db.DateTime)

Creating the table works fine so I think the class definition is fine. I inserted a few data in the database but to retrieve them, I have some issue and can't find the right syntax for doing it right.

Googling I found some keys but note work so far :

• Using func.any()
• Model.column_array.op("@>")(ARRAY("hello", "world")
• and a few others.

Thanks in advance for your help,

Nicolas

[Michael Bayer]

the "array()" function builds an ad-hoc array, its separate from the ARRAY type.

Using 0.8.0b1 (full release is soon) we have improved support for ARRAY:

http://docs.sqlalchemy.org/en/rel_0_8/dialects/postgresql.html?highlight=array#sqlalchemy.dialects.postgresql.ARRAY

http://docs.sqlalchemy.org/en/rel_0_8/dialects/postgresql.html?highlight=array#sqlalchemy.dialects.postgresql.array

[Audrius Kažukauskas]

On Thu, 2012-12-13 at 12:46:28 -0800, Michael Bayer wrote:
> the "array()" function builds an ad-hoc array, its separate from the ARRAY
> type.
>
> Using 0.8.0b1 (full release is soon) we have improved support for ARRAY:
>
> http://docs.sqlalchemy.org/en/rel_0_8/dialects/postgresql.html?highlight=array#sqlalchemy.dialects.postgresql.ARRAY
>
> http://docs.sqlalchemy.org/en/rel_0_8/dialects/postgresql.html?highlight=array#sqlalchemy.dialects.postgresql.array

In 0.7.9 (using psycopg2 as DBAPI driver) this works:

db.session.query(Mark.tags).filter(Mark.tags.op('@>')(['foo'])).all()

Although in 0.8 it would look nicer:

db.session.query(Mark.tags).filter(Mark.tags.contains(['foo'])).all()

--
Audrius Kažukauskas
http://neutrino.lt/

[Nicolas Steinmetz]

On Thursday, December 13, 2012 9:56:36 PM UTC+1, Audrius Kažukauskas wrote:

In 0.7.9 (using psycopg2 as DBAPI driver) this works:

  db.session.query(Mark.tags).filter(Mark.tags.op('@>')(['foo'])).all()

Although in 0.8 it would look nicer:

  db.session.query(Mark.tags).filter(Mark.tags.contains(['foo'])).all()

Thanks !

In fact i took literally "column_array" and did not transform it to "tags" in my example :-P

So it now works, I also had to change the type of tags from :

tags = db.Column(ARRAY(db.String))

to :

tags = db.Column(ARRAY(db.Text))

otherwise I had some issue when querying with character varying[] @> text[], it did not match ;-)

Thanks also to Michael for the array() in 0.8 but will wait for the stable release.

Have a nice week-end ahead,

Nicolas