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