Support Array of citext citext[] column

[jwoertink]

Avram currently supports Array(String), but if you need to search an array with case insensitive, then things get a little hairy

(assuming you have a GIN index on this column...)

=# select username from users where tags @> array['Lucky'];
  username   
-------------
 jeremy
(1 row)

Time: 0.438 ms
=# select username from users where tags @> array['LUCKY'];
  username   
-------------
(0 rows)

Time: 0.815 ms
=# select username from users where lower(tags::text)::text[] @> array['lucky'];
  username   
-------------
 jeremy
(1 row)

Time: 36.088 ms

Now if you use a citext[] column instead..

=# select username from users where tags @> array['LUCKY']::citext[];
  username   
-------------
 jeremy
(1 row)

Time: 0.395 ms
=# select username from users where tags @> array['lUcKy']::citext[];
  username   
-------------
 jeremy
(1 row)

Time: 0.408 ms

[jwoertink]

Ok, so apparently this is sort of supported already 🤯 You can create the column in your migrations already

alter table_for(User) do
  add tags : Array(String), default: [] of String, case_sensitive: false
end

table do
  column tags : Array(String)
end

Though, if you try to query with the column, you'll run in to some errors:

UserQuery.new.tags.includes("lucky").first
# Unhandled exception: Can't read column of type Array(Slice(UInt8)) as Array(String) (PG::RuntimeError)

I'm guessing some casting somewhere has to happen...

[jwoertink]

We may not be able to add this in until we get will/crystal-pg#250