Wie macht man eine "case-insensitive" Abfrage in Postgresql?

Question

Gibt es eine Möglichkeit, in PostgreSQL Abfragen ohne Berücksichtigung der Groß-/Kleinschreibung zu schreiben, z.B. möchte ich, dass die folgenden 3 Abfragen das gleiche Ergebnis liefern.

SELECT id FROM groups where name='administrator'

SELECT id FROM groups where name='ADMINISTRATOR'

SELECT id FROM groups where name='Administrator'

Answer 1

Verwenden Sie TIEFER Funktion, um die Zeichenketten vor dem Vergleich in Kleinbuchstaben umzuwandeln.

Versuchen Sie dies:

SELECT id 
  FROM groups
 WHERE LOWER(name)=LOWER('Administrator')

Answer 2

Die gängigste Vorgehensweise besteht darin, den Suchbegriff und die Daten entweder in Klein- oder Großbuchstaben zu schreiben. Dabei gibt es jedoch zwei Probleme.

1. Es funktioniert auf Englisch, aber nicht in allen Sprachen. (Vielleicht nicht einmal in meisten Sprachen.) Nicht jeder Kleinbuchstabe hat einen entsprechenden Großbuchstaben; nicht jeder Großbuchstabe hat einen entsprechenden Kleinbuchstaben.
2. Wenn Sie Funktionen wie lower() und upper() verwenden, erhalten Sie eine sequenzielle Scan. Es können keine Indizes verwendet werden. Auf meinem Testsystem dauert die Verwendung von lower() etwa 2000 Mal länger als eine Abfrage, die einen Index verwenden kann. (Die Testdaten haben etwas mehr als 100k Zeilen.)

Es gibt mindestens drei weniger häufig verwendete Lösungen, die wirksamer sein könnten.

1. Verwenden Sie die Zitext-Modul die das Verhalten eines Datentyps ohne Berücksichtigung der Groß- und Kleinschreibung weitgehend nachahmt. Nachdem Sie dieses Modul geladen haben, können Sie einen case-insensitive Index erstellen, indem Sie CREATE INDEX ON groups (name::citext); . (Aber siehe unten.)
2. Verwenden Sie eine Sortierung, die Groß- und Kleinschreibung nicht berücksichtigt. Dies wird beim Initialisieren einer Datenbank. Die Verwendung einer Sortierung, die Groß- und Kleinschreibung nicht berücksichtigt, bedeutet, dass Sie Format vom Client-Code akzeptieren, und Sie geben trotzdem nützliche Ergebnisse. (Es bedeutet auch, dass Sie keine Abfragen unter Berücksichtigung der Groß-/Kleinschreibung durchführen können.)
3. Erstellen Sie einen funktionalen Index. Erstellen Sie einen Index mit Kleinbuchstaben, indem Sie CREATE INDEX ON groups (LOWER(name)); . Danach können Sie die Vorteile des Indexes mit Abfragen wie SELECT id FROM groups WHERE LOWER(name) = LOWER('ADMINISTRATOR'); o SELECT id FROM groups WHERE LOWER(name) = 'administrator'; Sie müssen erinnern Sie sich an LOWER() zu verwenden.

---

Das citext-Modul bietet keinen Datentyp, bei dem die Groß-/Kleinschreibung keine Rolle spielt. Stattdessen verhält es sich so, als ob jede Zeichenkette kleingeschrieben wäre. Das heißt, es verhält sich so, als hätten Sie den Befehl lower() für jeden String, wie in Nummer 3 oben. Der Vorteil ist, dass die Programmierer nicht daran denken müssen, die Zeichenketten klein zu schreiben. Sie müssen jedoch die Abschnitte "String Comparison Behavior" und "Limitations" in den Dokumenten lesen, bevor Sie sich für die Verwendung von citext entscheiden.

Answer 3

Sie können sich auch über die ILIKE Stichwort. Es kann mitunter recht nützlich sein, auch wenn es nicht dem SQL-Standard entspricht. Siehe hier für weitere Informationen: http://www.postgresql.org/docs/9.2/static/functions-matching.html

Answer 4

Utilice ILIKE

select id from groups where name ILIKE 'adminstration';

Wenn Sie den expressjs-Hintergrund verwenden und name eine Variable ist verwenden Sie

select id from groups where name ILIKE $1;

Answer 5

Verwendung von ~* kann die Leistung mit der Funktionalität von INSTR erheblich verbessern.

SELECT id FROM groups WHERE name ~* 'adm'

gibt Zeilen mit Namen zurück, die 'adm' enthalten oder gleich sind.