Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- text_pattern_ops
CREATE TABLE book1 (
   book_id SERIAL PRIMARY KEY,
   book text NOT NULL,
   CONSTRAINT book1_uni UNIQUE (book)  -- without text_pattern_ops
);

CREATE UNIQUE INDEX book1_pattern_ops ON book1 (book text_pattern_ops); -- with text_pattern_ops


-- COLLATE "C"
CREATE TABLE book2 (
   book_id SERIAL PRIMARY KEY,
   book text NOT NULL COLLATE "C" UNIQUE  -- that's all!
);

-- Generate dummy rows
INSERT INTO book2 (book)
SELECT string_agg(char, '') || rn
FROM  (
   SELECT chr((random() * 100)::int + 32) AS char
        , row_number() OVER () / 7 AS rn
   FROM   generate_series (1,70000) g
   ) sub
GROUP  BY rn
UNION  ALL  -- add some rows manually
VALUES ('foo'::text), ('Foo'), ('bar'), ('FOObar'), ('footaststic');
10006 rows affected

-- wouldn't work!
ALTER TABLE book1 DROP CONSTRAINT book1_uni
                , ADD  CONSTRAINT book1_uni UNIQUE USING INDEX book1_pattern_ops;
ERROR:  index "book1_pattern_ops" column number 1 does not have default sorting behavior
LINE 3:                 , ADD  CONSTRAINT book1_uni UNIQUE USING IND...
                               ^
DETAIL:  Cannot create a primary key or unique constraint using such an index.


-- case sensitive
INSERT INTO book1 (book) VALUES ('foo'), ('Foo'), ('bar');
SELECT * FROM book1 WHERE book ILIKE 'foo%';
3 rows affected



book_id
book


1
foo

2
Foo


SELECT * FROM book2 LIMIT 5;


book_id
book


1
V4Ht,F,184

2
6La3VR<87

3
kk$@=F477

4
uIC;BGZ273

5
p|4,C394


-- LIKE can use the index:
SELECT * FROM book2 WHERE book LIKE 'foo%';


book_id
book


10002
foo

10006
footaststic


-- But ILIKE can't:
SELECT * FROM book2 WHERE book ILIKE 'foo%';


book_id
book


10002
foo

10003
Foo

10005
FOObar

10006
footaststic