Barcodes should be case insensitive

Here's a query that will identify patron barcodes that are duplicates, except for case.

select a.usr, a.id, a.barcode, b.usr, b.id, b.barcode
from actor.card a
join actor.card b
on lower(a.barcode) = lower(b.barcode)

We have over a hundred of these. Several belong to the same user.

And, here's one for copy barcodes:

select a.id, a.barcode, b.id, b.barcode
from asset.copy a
join asset.copy b
on lower(a.barcode) = lower(b.barcode)
and b.id > a.id
and not a.deleted and not b.deleted;

We have even more of these, over 600 rows returned.

and a.id < b.id;

Oops! Looks like my first query got chopped up. It should be:

select a.usr, a.id, a.barcode, b.usr, b.id, b.barcode
from actor.card a
join actor.card b
on lower(a.barcode) = lower(b.barcode)
and a.id < b.id;

Postgres has an included module called citext that may be useful if we move forward with this.
https://www.postgresql.org/docs/13/citext.html

But I think that would make it a non optional features.

I found a few patron barcodes that were conflicting, but they are all examples of situations that shouldn't be allowed anyway, or work arounds.

No copy barcodes in our system that differ only in case.

On my 3.11.0 test system there are two index entries for actor.card(barcode).

    "actor_card_barcode_evergreen_lowercase_idx" btree (lowercase(barcode))
    "card_barcode_key" UNIQUE CONSTRAINT, btree (barcode)

We have added student cards recently, that use a school district alpha prefix, so it is a bit of a pain that students need to always enter the prefix in caps for it to work.

ABCD4567 not being equal to abcd4567.

Josh

Adding links to a couple related bugs:

bug 1581658 - OPAC: Barcode that starts with a letter cannot login to My Account

bug 1332651 - Screens are inconsistent when there is a space within the barcode of items or patrons

The latter addresses spaces rather than case, but the discussion is relevant.