A few days ago we were implementing a Spring Boot application which stored its data into a PostgreSQL database. As a requirement we needed to search into the database with LIKE operator in our
SQL queries and at the same time perform case and accent insensitive searches. Since we are Greek, this is pretty typical for us because our letters have diacritics (“tonos”). So a query like the following
select * from blablatable where name like '%χρη%' ;
should return results for:
The aforementioned SQL query works perfectly normal in a MySQL or MariaDB database with collation
utf8_general_ci. However in PostgreSQL this is not the case. So after some searching and reading StackOverflow #11005036, we figured out how to make case and accent insensitive search SQL queries.
Fire up a PostgreSQL Docker container
We chose to use PostgreSQL inside a Docker container, so we fire up one:
docker run --name postgres \ -p 5432:5432 \ -e POSTGRES_PASSWORD=strongpassword \ -e POSTGRES_USER=stronguser \ -e POSTGRES_DB=airplanes \ -d postgres:13.0
Connect to PostgreSQL using a Docker container
docker run -it --rm postgres:13.0 psql -h host.docker.internal -d airplanes --user stronguser Password for user stronguser: <enter thy password>
host.docker.internal. This host name is used only in Docker for Windows to mark the host computer where Docker runs.
Create an extension in PostgreSQL
In order to use case and accent insensitive search SQL queries, we need to use a PostgreSQL extension. So, execute in your favourite PostgreSQL client:
CREATE EXTENSION unaccent;
Create a table and insert some data
Then we need to create a simple table in the default schema
public, which will have some text data with accents:
create sequence public.BOBOS_SEQUENCE start 1 increment 1; create table public.bobos ( ID int8 not null DEFAULT nextval('BOBOS_SEQUENCE'), name varchar(255), primary key (ID) );
After that we populate it with some test data:
INSERT INTO public.bobos (name) VALUES('Τάκης, the best handmade gyros of Triandria'); INSERT INTO public.bobos (name) VALUES('Χρήστος'); INSERT INTO public.bobos (name) VALUES('ΧΡΗΣΤΟΣ'); INSERT INTO public.bobos (name) VALUES('χΡήστος');
SQL case and accent insensitive queries
Finally in order to perform case and accent insensitive SQL queries , execute the following statement:
select * from public.bobos where unaccent(LOWER(NAME)) like unaccent(LOWER('%χρη%')) ;
The results will be like the following:
id|name | --|-------| 2|Χρήστος| 3|ΧΡΗΣΤΟΣ| 4|χΡήστος|
As you might have noticed, we need to use both
unaccent() extension and
LOWER() function in order to meet the desired result.
I hope you found this article useful and you do not waste any more time searching in the web. Have a nice day and love thy neighbour!