- changelog for #2785

- refactor tests a bit
fixes #2785

3 files changed, 32 insertions, 32 deletions

diff --git a/doc/build/changelog/changelog_09.rst b/doc/build/changelog/changelog_09.rst
index 14ef6a601..bb9da76af 100644
--- a/doc/build/changelog/changelog_09.rst
+++ b/doc/build/changelog/changelog_09.rst
@@ -15,6 +15,17 @@
     :version: 0.9.5
 
     .. change::
+        :tags: feature, postgresql
+        :tickets: 2785
+        :pullreq: bitbucket:18
+
+        Added a new flag :paramref:`.ARRAY.zero_indexes` to the Postgresql
+        :class:`.ARRAY` type.  When set to ``True``, a value of one will be
+        added to all array index values before passing to the database, allowing
+        better interoperability between Python style zero-based indexes and
+        Postgresql one-based indexes.  Pull request courtesy Alexey Terentev.
+
+    .. change::
         :tags: bug, engine
         :tickets: 3043
 
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 50918c31e..bcbf0b12c 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -834,9 +834,12 @@ class ARRAY(sqltypes.Concatenable, sqltypes.TypeEngine):
          meaning they can store any number of dimensions no matter how
          they were declared.
 
-         :param zero_indexes=False: True allow to work with field like with
-          python's list - use indexes starts with zero, but not starts with
-          1 like in ARRAY
+        :param zero_indexes=False: when True, index values will be converted
+         between Python zero-based and Postgresql one-based indexes, e.g.
+         a value of one will be added to all index values before passing
+         to the database.
+
+         .. versionadded:: 0.9.5
 
         """
         if isinstance(item_type, ARRAY):
diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index 35f7b1199..c81389385 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -428,58 +428,44 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             checkparams={'param_1': 7}
         )
 
-    def test_array_shift_indexes(self):
-        c = Column('x', postgresql.ARRAY(Integer, zero_indexes=True))
+    def _test_array_zero_indexes(self, zero_indexes):
+        c = Column('x', postgresql.ARRAY(Integer, zero_indexes=zero_indexes))
+
+        add_one = 1 if zero_indexes else 0
 
         self.assert_compile(
-            cast(c, postgresql.ARRAY(Integer, zero_indexes=True)),
+            cast(c, postgresql.ARRAY(Integer, zero_indexes=zero_indexes)),
             "CAST(x AS INTEGER[])"
         )
         self.assert_compile(
             c[5],
             "x[%(x_1)s]",
-            checkparams={'x_1': 6}
+            checkparams={'x_1': 5 + add_one}
         )
 
         self.assert_compile(
             c[5:7],
             "x[%(x_1)s:%(x_2)s]",
-            checkparams={'x_2': 8, 'x_1': 6}
+            checkparams={'x_2': 7 + add_one, 'x_1': 5 + add_one}
         )
         self.assert_compile(
             c[5:7][2:3],
             "x[%(x_1)s:%(x_2)s][%(param_1)s:%(param_2)s]",
-            checkparams={'x_2': 8, 'x_1': 6, 'param_1': 3, 'param_2': 4}
+            checkparams={'x_2': 7 + add_one, 'x_1': 5 + add_one,
+                'param_1': 2 + add_one, 'param_2': 3 + add_one}
         )
         self.assert_compile(
             c[5:7][3],
             "x[%(x_1)s:%(x_2)s][%(param_1)s]",
-            checkparams={'x_2': 8, 'x_1': 6, 'param_1': 4}
+            checkparams={'x_2': 7 + add_one, 'x_1': 5 + add_one,
+                    'param_1': 3 + add_one}
         )
 
-        c = Column('x', postgresql.ARRAY(Integer, zero_indexes=False))
-
-        self.assert_compile(
-            c[5],
-            "x[%(x_1)s]",
-            checkparams={'x_1': 5}
-        )
+    def test_array_zero_indexes_true(self):
+        self._test_array_zero_indexes(True)
 
-        self.assert_compile(
-            c[5:7],
-            "x[%(x_1)s:%(x_2)s]",
-            checkparams={'x_2': 7, 'x_1': 5}
-        )
-        self.assert_compile(
-            c[5:7][2:3],
-            "x[%(x_1)s:%(x_2)s][%(param_1)s:%(param_2)s]",
-            checkparams={'x_2': 7, 'x_1': 5, 'param_1': 2, 'param_2': 3}
-        )
-        self.assert_compile(
-            c[5:7][3],
-            "x[%(x_1)s:%(x_2)s][%(param_1)s]",
-            checkparams={'x_2': 7, 'x_1': 5, 'param_1': 3}
-        )
+    def test_array_zero_indexes_false(self):
+        self._test_array_zero_indexes(False)
 
     def test_array_literal_type(self):
         is_(postgresql.array([1, 2]).type._type_affinity, postgresql.ARRAY)