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