Render ARRAY index embedded between type and COLLATE

Fixed bug where using :class:`.ARRAY` with a string type that
features a collation would fail to produce the correct syntax
within CREATE TABLE.

The "COLLATE" must appear to the right of the array dimensions,
so we are using regexp substitution to insert the brackets in the
appropriate place.  A more heavyweight solution would be that datatypes
know how to split up their base type vs. modifiers, but as this is
so specific to Postgresql ARRAY it's better to handle these cases
more locally.

Change-Id: I394c3c673eb60689e51b5301e51651972cfdb4c0
Fixes: #4006

3 files changed, 41 insertions, 3 deletions

diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst
index c0e3c18d6..612dda08a 100644
--- a/doc/build/changelog/changelog_11.rst
+++ b/doc/build/changelog/changelog_11.rst
@@ -34,6 +34,15 @@
         amount of risk to compatibility w/ older or alternate Postgresql
         databases.
 
+    .. change:: 4006
+        :tags: bug, postgresql
+        :tickets: 4006
+        :versions: 1.2.0b1
+
+        Fixed bug where using :class:`.ARRAY` with a string type that
+        features a collation would fail to produce the correct syntax
+        within CREATE TABLE.
+
     .. change:: 3994
         :tags: bug, mssql
         :tickets: 3994
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index e583bd5cf..b56ac5b10 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1873,9 +1873,15 @@ class PGTypeCompiler(compiler.GenericTypeCompiler):
         return "BYTEA"
 
     def visit_ARRAY(self, type_, **kw):
-        return self.process(type_.item_type) + ('[]' * (type_.dimensions
-                                                        if type_.dimensions
-                                                        is not None else 1))
+
+        # TODO: pass **kw?
+        inner = self.process(type_.item_type)
+        return re.sub(
+            r'((?: COLLATE.*)?)$',
+            (r'[]\1' *
+             (type_.dimensions if type_.dimensions is not None else 1)),
+            inner
+        )
 
 
 class PGIdentifierPreparer(compiler.IdentifierPreparer):
diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py
index d2e19a04a..b157070c5 100644
--- a/test/dialect/postgresql/test_types.py
+++ b/test/dialect/postgresql/test_types.py
@@ -830,6 +830,18 @@ class TimePrecisionTest(fixtures.TestBase, AssertsCompiledSQL):
 class ArrayTest(AssertsCompiledSQL, fixtures.TestBase):
     __dialect__ = 'postgresql'
 
+    def test_array_type_render_str(self):
+        self.assert_compile(
+            postgresql.ARRAY(Unicode(30)),
+            "VARCHAR(30)[]"
+        )
+
+    def test_array_type_render_str_collate(self):
+        self.assert_compile(
+            postgresql.ARRAY(Unicode(30, collation="en_US")),
+            'VARCHAR(30)[] COLLATE "en_US"'
+        )
+
     def test_array_int_index(self):
         col = column('x', postgresql.ARRAY(Integer))
         self.assert_compile(
@@ -1066,6 +1078,17 @@ class ArrayRoundTripTest(object):
         assert isinstance(tbl.c.strarr.type.item_type, String)
 
     @testing.provide_metadata
+    def test_array_str_collation(self):
+        m = self.metadata
+
+        t = Table(
+            't', m, Column('data',
+                           sqltypes.ARRAY(String(50, collation="en_US")))
+        )
+
+        t.create()
+
+    @testing.provide_metadata
     def test_array_agg(self):
         values_table = Table('values', self.metadata, Column('value', Integer))
         self.metadata.create_all(testing.db)