From ec422fb70e0044ed42dcfda5fb1a7a65db322cf1 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Thu, 8 Jun 2017 12:55:23 -0400
Subject: [PATCH] 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
---
 doc/build/changelog/changelog_11.rst       |  9 +++++++++
 lib/sqlalchemy/dialects/postgresql/base.py | 12 ++++++++---
 test/dialect/postgresql/test_types.py      | 23 ++++++++++++++++++++++
 3 files changed, 41 insertions(+), 3 deletions(-)

diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst
index c0e3c18d62..612dda08a5 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 e583bd5cff..b56ac5b105 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 d2e19a04a2..b157070c5f 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(
@@ -1065,6 +1077,17 @@ class ArrayRoundTripTest(object):
         assert isinstance(tbl.c.intarr.type.item_type, Integer)
         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))
-- 
2.47.2