Merge pull request #26208 from nanaya/pg-insensitive-text

Fix case insensitive check for text column in pg

3 files changed, 37 insertions, 2 deletions

diff --git a/activerecord/CHANGELOG.md b/activerecord/CHANGELOG.md
index 879c4a87cf..15b49e0a0b 100644
--- a/activerecord/CHANGELOG.md
+++ b/activerecord/CHANGELOG.md
@@ -1,3 +1,8 @@
+*   Fixed support for case insensitive comparisons of `text` columns in
+    PostgreSQL.
+
+    *Edho Arief*
+
 *   Made ActiveRecord consistently use `ActiveRecord::Type` (not `ActiveModel::Type`)
 
     *Iain Beeston*
diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
index 8001c0dd53..a33e64883e 100644
--- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
@@ -771,10 +771,14 @@ module ActiveRecord
             sql = <<-end_sql
               SELECT exists(
                 SELECT * FROM pg_proc
+                WHERE proname = 'lower'
+                  AND proargtypes = ARRAY[#{quote column.sql_type}::regtype]::oidvector
+              ) OR exists(
+                SELECT * FROM pg_proc
                 INNER JOIN pg_cast
-                  ON casttarget::text::oidvector = proargtypes
+                  ON ARRAY[casttarget]::oidvector = proargtypes
                 WHERE proname = 'lower'
-                  AND castsource = '#{column.sql_type}'::regtype::oid
+                  AND castsource = #{quote column.sql_type}::regtype
               )
             end_sql
             execute_and_clear(sql, "SCHEMA", []) do |result|
diff --git a/activerecord/test/cases/adapters/postgresql/case_insensitive_test.rb b/activerecord/test/cases/adapters/postgresql/case_insensitive_test.rb
new file mode 100644
index 0000000000..d04e55f5bf
--- /dev/null
+++ b/activerecord/test/cases/adapters/postgresql/case_insensitive_test.rb
@@ -0,0 +1,26 @@
+require "cases/helper"
+
+class PostgresqlCaseInsensitiveTest < ActiveRecord::PostgreSQLTestCase
+  class Default < ActiveRecord::Base; end
+
+  def test_case_insensitiveness
+    connection = ActiveRecord::Base.connection
+    table = Default.arel_table
+
+    column = Default.columns_hash["char1"]
+    comparison = connection.case_insensitive_comparison table, :char1, column, nil
+    assert_match /lower/i, comparison.to_sql
+
+    column = Default.columns_hash["char2"]
+    comparison = connection.case_insensitive_comparison table, :char2, column, nil
+    assert_match /lower/i, comparison.to_sql
+
+    column = Default.columns_hash["char3"]
+    comparison = connection.case_insensitive_comparison table, :char3, column, nil
+    assert_match /lower/i, comparison.to_sql
+
+    column = Default.columns_hash["multiline_default"]
+    comparison = connection.case_insensitive_comparison table, :multiline_default, column, nil
+    assert_match /lower/i, comparison.to_sql
+  end
+end