Array index in postgres ARRAy

[Former user]

Hi.

When I work with SQLAlchemy and PostgreSQL I find interesting and unobvious nuance:

When using item of array in filter or order_by there are need to use indexes starts from 1 (there are no index 0 in pestgres ARRAY) But, for getting value or values and use it there are need to use indexes starts from zero.

May be would be better change logic of getitem method of sqlalchemy.dialects.postgresql.ARRAY for use one standart of indexes in all situations.

Regards, Alexey Terentev

[Mike Bayer]

yeah especially in terms of SQLAlchemy hybrid attributes. But we can't make this a default change, it has to be a flag. Can you work up a pull request for a feature like this?

Column('data', ARRAY(Integer, zero_indexed=True))

[Mike Bayer]

I think we'd want to make the zero index adjustment against literal Python values only, not in SQL. like if someone said table.c.somecolumn[someother_table.c.column](someother_table.c.column), assume someother_table.c.column is one-based. Though I think there's lots of potentially untenable situations here if someone really makes heavy use of hybrid behavior in conjunction with array indexing, ultimately in such a situation they may have to build their own comparators to work things out as needed.

[Alexey Terentev]

code review? https://bitbucket.org/LevonXXL/sqlalchemy/commits/043bc5fcb629b0275ef05e1b72515f1e5ea8ce9b

[Alexey Terentev]

https://bitbucket.org/LevonXXL/sqlalchemy/commits/57b6da9d402f164605c7bfe270654725cb604f1f

I did push new commit via rebase and squash - this with changes from comments

[Mike Bayer]

should be good, send a pullreq! thanks

[Alexey Terentev]

Which branch select for create pull-request into?

master or any other?

[Mike Bayer]

typically master.

[Mike Bayer]

• changelog for #2785
• refactor tests a bit fixes #2785

→ <<cset 460465a8749a>>

[Mike Bayer]

OK thanks!

[Alexey Terentev]

thank you!