PostgreSQL: `:collation` support for string and text columns

Example:

    create_table :foos do |t|
      t.string :string_en, collation: 'en_US.UTF-8'
      t.text   :text_ja,   collation: 'ja_JP.UTF-8'
    end

4 files changed, 79 insertions, 3 deletions

diff --git a/activerecord/CHANGELOG.md b/activerecord/CHANGELOG.md
index 923d780b90..35f760d63a 100644
--- a/activerecord/CHANGELOG.md
+++ b/activerecord/CHANGELOG.md
@@ -1,3 +1,14 @@
+*   PostgreSQL: `:collation` support for string and text columns.
+
+    Example:
+
+        create_table :foos do |t|
+          t.string :string_en, collation: 'en_US.UTF-8'
+          t.text   :text_ja,   collation: 'ja_JP.UTF-8'
+        end
+
+    *Ryuta Kamizono*
+
 *   Make `unscope` aware of "less than" and "greater than" conditions.
 
     *TAKAHASHI Kazuaki*
diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb
index bb944c7cfd..662c6b4d38 100644
--- a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb
+++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb
@@ -8,6 +8,13 @@ module ActiveRecord
           o.sql_type = type_to_sql(o.type, o.limit, o.precision, o.scale, o.array)
           super
         end
+
+        def add_column_options!(sql, options)
+          if options[:collation]
+            sql << " COLLATE \"#{options[:collation]}\""
+          end
+          super
+        end
       end
 
       module SchemaStatements
@@ -159,13 +166,13 @@ module ActiveRecord
         # Returns the list of all column definitions for a table.
         def columns(table_name)
           # Limit, precision, and scale are all handled by the superclass.
-          column_definitions(table_name).map do |column_name, type, default, notnull, oid, fmod|
+          column_definitions(table_name).map do |column_name, type, default, notnull, oid, fmod, collation|
             oid = oid.to_i
             fmod = fmod.to_i
             type_metadata = fetch_type_metadata(column_name, type, oid, fmod)
             default_value = extract_value_from_default(default)
             default_function = extract_default_function(default_value, default)
-            new_column(column_name, default_value, type_metadata, !notnull, default_function)
+            new_column(column_name, default_value, type_metadata, !notnull, default_function, collation)
           end
         end
 
@@ -409,6 +416,9 @@ module ActiveRecord
           quoted_column_name = quote_column_name(column_name)
           sql_type = type_to_sql(type, options[:limit], options[:precision], options[:scale], options[:array])
           sql = "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quoted_column_name} TYPE #{sql_type}"
+          if options[:collation]
+            sql << " COLLATE \"#{options[:collation]}\""
+          end
           if options[:using]
             sql << " USING #{options[:using]}"
           elsif options[:cast_as]
diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
index 332ac9d88c..7e15c2ab26 100644
--- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
@@ -772,7 +772,9 @@ module ActiveRecord
         def column_definitions(table_name) # :nodoc:
           exec_query(<<-end_sql, 'SCHEMA').rows
               SELECT a.attname, format_type(a.atttypid, a.atttypmod),
-                     pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod
+                     pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod,
+             (SELECT c.collname FROM pg_collation c, pg_type t
+               WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation)
                 FROM pg_attribute a LEFT JOIN pg_attrdef d
                   ON a.attrelid = d.adrelid AND a.attnum = d.adnum
                WHERE a.attrelid = '#{quote_table_name(table_name)}'::regclass
diff --git a/activerecord/test/cases/adapters/postgresql/collation_test.rb b/activerecord/test/cases/adapters/postgresql/collation_test.rb
new file mode 100644
index 0000000000..17ef5f304c
--- /dev/null
+++ b/activerecord/test/cases/adapters/postgresql/collation_test.rb
@@ -0,0 +1,53 @@
+require "cases/helper"
+require 'support/schema_dumping_helper'
+
+class PostgresqlCollationTest < ActiveRecord::TestCase
+  include SchemaDumpingHelper
+
+  def setup
+    @connection = ActiveRecord::Base.connection
+    @connection.create_table :postgresql_collations, force: true do |t|
+      t.string :string_c, collation: 'C'
+      t.text :text_posix, collation: 'POSIX'
+    end
+  end
+
+  def teardown
+    @connection.drop_table :postgresql_collations, if_exists: true
+  end
+
+  test "string column with collation" do
+    column = @connection.columns(:postgresql_collations).find { |c| c.name == 'string_c' }
+    assert_equal :string, column.type
+    assert_equal 'C', column.collation
+  end
+
+  test "text column with collation" do
+    column = @connection.columns(:postgresql_collations).find { |c| c.name == 'text_posix' }
+    assert_equal :text, column.type
+    assert_equal 'POSIX', column.collation
+  end
+
+  test "add column with collation" do
+    @connection.add_column :postgresql_collations, :title, :string, collation: 'C'
+
+    column = @connection.columns(:postgresql_collations).find { |c| c.name == 'title' }
+    assert_equal :string, column.type
+    assert_equal 'C', column.collation
+  end
+
+  test "change column with collation" do
+    @connection.add_column :postgresql_collations, :description, :string
+    @connection.change_column :postgresql_collations, :description, :text, collation: 'POSIX'
+
+    column = @connection.columns(:postgresql_collations).find { |c| c.name == 'description' }
+    assert_equal :text, column.type
+    assert_equal 'POSIX', column.collation
+  end
+
+  test "schema dump includes collation" do
+    output = dump_table_schema("postgresql_collations")
+    assert_match %r{t.string\s+"string_c",\s+collation: "C"$}, output
+    assert_match %r{t.text\s+"text_posix",\s+collation: "POSIX"$}, output
+  end
+end