Array index in postgres ARRAy

Issue #2785 resolved
Anonymous created an issue


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

Comments (12)

  1. Michael Bayer repo owner

    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))
  2. Michael Bayer repo owner

    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.

  3. Log in to comment