Issues

Issue #2785 resolved

Array index in postgres ARRAy

Anonymous created an issue

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

Comments (12)

  1. Mike 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. Mike 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