Case-insensitive search in YSQL is a much-requested feature, most likely to ease the migration from Microsoft SQL Server to YugabyteDB.
You can accomplish case-insensitve search in YSQL the tried and true method of converting the text to either UPPER or LOWER case on each side of the predicate equation.
Example:
yugabyte=# SELECT * FROM t;
c1
----
A
B
a
(3 rows)
yugabyte=# SELECT * FROM t WHERE c1 = 'A';
c1
----
A
(1 row)
yugabyte=# SELECT * FROM t WHERE UPPER(c1) = 'A';
c1
----
A
a
(2 rows)
yugabyte=# SELECT * FROM t WHERE LOWER(c1) = 'a';
c1
----
A
a
(2 rows)
Another option could be to install the PostgreSQL extension CITEXT that is bundled with YugabyteDB.
Once you’ve install the extension, you can create tables having columns defined using the user defined data type of CITEXT.
Example:
yugabyte=# CREATE EXTENSION citext;
CREATE EXTENSION
yugabyte=# \d t
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
c1 | character varying | | |
yugabyte=# ALTER TABLE t ADD COLUMN c2 CITEXT;
ALTER TABLE
yugabyte=# UPDATE t SET c2 = c1;
UPDATE 3
yugabyte=# SELECT * FROM t;
c1 | c2
----+----
A | A
B | B
a | a
(3 rows)
yugabyte=# SELECT * FROM t WHERE c2 = 'a';
c1 | c2
----+----
A | A
a | a
(2 rows)
yugabyte=# SELECT * FROM t WHERE c2 = 'A';
c1 | c2
----+----
A | A
a | a
(2 rows)
But before you get too excited, there is one big limitation on table columns having the CITEXT data type in YugabyteDB… You cannot create an index on it.
yugabyte=# CREATE INDEX t_c2_idx ON t(c2);
ERROR: INDEX on column of type 'user_defined_type' not yet supported
You can however create a function based index when using the UPPER or LOWER route to do case insentive queries… But this might require code changes.
yugabyte=# CREATE INDEX t_c2_idx ON t(UPPER(c1));
CREATE INDEX
yugabyte=# EXPLAIN SELECT * FROM t WHERE UPPER(c1) = 'A';
QUERY PLAN
--------------------------------------------------------------------
Index Scan using t_c2_idx on t (cost=0.00..5.25 rows=10 width=64)
Index Cond: (upper((c1)::text) = 'A'::text)
(2 rows)
Have Fun!
