search

Jaymon / prom

A PostgreSQL or SQLite orm for Python
MIT License
22 stars 4 forks source link

A better way to do Postgres case-insensitive searching? #146

Closed Jaymon closed 1 year ago

Jaymon commented 2 years ago

Currently we basically do this but it is probably worth revisiting this and taking a good look at citext or collation (which is how we do it on SQLite).

Jarid was researching this in July 2022 for something on Bodega and is the one that showed me citext.

Jaymon commented 1 year ago

Just in case I forget how to do this. You need to create the CITEXT extension on the table you want to use CITEXT on, and you have to be a super user to do it.

So connect to the db you want to add CITEXT to:

$ psql -U postgres -h <DBHOST> -d <DBNAME>

And then install the extension:

<DBNAME>=> CREATE EXTENSION IF NOT EXISTS citext;

You can check if the extension is on the table by running:

<DBNAME>=> select extname from pg_extension;