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:
CREATE TABLE t(c1 TEXT);
INSERT INTO t VALUES ('A'), ('B'), ('a');
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:
CREATE EXTENSION citext;
ALTER TABLE t ADD COLUMN c2 CITEXT;
UPDATE t SET c2 = c1;
SELECT * FROM t;
SELECT * FROM t WHERE c2 = 'a';
SELECT * FROM t WHERE c2 = 'A';
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.
CREATE INDEX t_c2_idx ON t(c2);
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.
CREATE INDEX t_c2_idx ON t(UPPER(c2));
EXPLAIN SELECT * FROM t WHERE UPPER(c2) = 'A';
yugabyte=# CREATE INDEX t_c2_idx ON t(UPPER(c2));
CREATE INDEX
yugabyte=# EXPLAIN SELECT * FROM t WHERE UPPER(c2) = '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)
🤔 So, What’s the Point of CITEXT?
At this point, you may be wondering:
- If I have to use
UPPER(c2)orLOWER(c2)in my query to take advantage of an index, what is the point of makingc2aCITEXTcolumn in the first place?
That is a fair question.
The main attraction of PostgreSQL’s CITEXT data type is that case-insensitive comparisons happen automatically. You can write a simple predicate like:
WHERE c2 = 'A';
and let CITEXT handle the case-insensitive comparison automatically.
However, because YugabyteDB currently cannot create a regular index directly on a CITEXT column, indexed searches require a functional index such as:
CREATE INDEX t_c2_idx ON t (UPPER(c2));
The query must then use the same expression:
WHERE UPPER(c2) = 'A';
At that point, much of the convenience that made CITEXT attractive in the first place has been lost.
💡 Where Does
CITEXT Still Make Sense?The most practical use for
CITEXT in YugabyteDB today is PostgreSQL migration compatibility. If an existing schema, application, ORM, or tool already expects the CITEXT data type, keeping it can make the migration easier and avoid unnecessary application or schema changes.⚠️ Bottom Line
CITEXT still provides case-insensitive comparison semantics in YSQL, but once indexed searches or uniqueness are required, its biggest advantage, transparent, simple case-insensitive queries, is significantly reduced.Have Fun!
