How to replace Postgres array elements using SQLAlchemy ORM?

Question

Suppose we have a Postgres table with an integer array column xs:

create table mytable (
    id bigserial primary key,
    xs int[]
);

Using Postgres 9.4 and SQLAlchemy 1.0.14, I want to replace a single value (for example 123) in all of rows in xs column. What I have now is a raw SQL query that looks like

update mytable set xs = array_replace(xs, 123, 456) where 123 = any(xs)

The 123 = any(xs) is just to speed up query as otherwise we would replace all rows not regarding whether the interesting value is in the array or not.

How could I rewrite the above query using the SQLAlchemy ORM when I already have MyTable mapped?

Ilja Everilä · Accepted Answer · 2017-02-14 11:07:08Z

4

Assuming you're using the correct postgresql.ARRAY type:

from sqlalchemy import  func

session.query(MyTable).\
    filter(MyTable.xs.any(123)).\
    update({MyTable.xs: func.array_replace(MyTable.xs, 123, 456)},
           synchronize_session=False)

Change the synchronization as necessary.

edited Feb 14, 2017 at 11:07

answered Feb 14, 2017 at 10:56

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Timo Over a year ago

Great answer! Thanks!

Collectives™ on Stack Overflow

How to replace Postgres array elements using SQLAlchemy ORM?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related