Re: insensitive collations

Peter Eisentraut wrote:

> Here is an updated patch.

On a table with pre-existing contents, the creation of a unique index
does not seem to detect the duplicates that are equal per the
collation and different binary-wise.

postgres=# \d test3ci
Table "public.test3ci"
Column | Type | Collation | Nullable | Default
--------+------+------------------+----------+---------
x | text | case_insensitive | |

postgres=# select * from test3ci;
x
-----
abc
ABC
def
ghi
(4 rows)

postgres=# create unique index idx on test3ci(x); -- EXPECTED TO FAIL
CREATE INDEX

postgres=# \d test3ci
Table "public.test3ci"
Column | Type | Collation | Nullable | Default
--------+------+------------------+----------+---------
x | text | case_insensitive | |
Indexes:
"idx" UNIQUE, btree (x)

postgres=# select count(*) from test3ci where x='abc';
count
-------
2
(1 row)

OTOH with an already existing unique index, attempts to insert
such duplicates are rejected as expected.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite