Order Strings with polish in Postgres

Question

What is the best way to order Entities by some String field that contains polish letters? Is there a way to do that with Spring Data? Can I include Locale into Pageabe for this?:

Page<Collection> findByInstitutionIdAndIsDeletedFalse(Long institutionId, Pageable pageable);

and

Sort.Order entityOrder = Sort.Order.by("title").ignoreCase();
PageRequest pageable = PageRequest.of(page, perPage, Sort.by(entityOrder));

When I do like that, I have:

alaska
lalka
termos
łóżko

But "łóżko" should be after "lalka".

I tried to change Locale in Postgres database, but it didn't work for db1 nor db2.

   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 db1       | user     | UTF8     | pl_PL      | pl_PL      | 
 db2       | user     | UTF8     | pl_PL.utf8 | pl_PL.utf8 | 
 db3       | user     | UTF8     | en_US.utf8 | en_US.utf8 |

Thanks for any suggestions!

activate SQL log and display them. I suspect that the "order by" is missing. — pdem, Jul 28 '20 at 14:32
As ordering in spring-data is being done on the database side setting right collation for the database should do the trick. Why do you claim it doesn't work? — Cezary Butler, Jul 28 '20 at 14:35
@pdem "order by" works just fine, but it doesn't care about polish letters. I edited my question to be more specific. — danny, Jul 28 '20 at 14:53
So It is postgres related, just try the SQL query, maybe the locale is not installed, see this: https://stackoverflow.com/questions/18932922/postgresql-sorting-language-specific-characters-collation — pdem, Jul 28 '20 at 15:39
i would try creating a fresh DB with the correct locale. IIRC changing locale on an existing DB didn't work for me either. — Robert Niestroj, Jul 29 '20 at 08:53

score 0 · Accepted Answer · answered Aug 03 '20 at 13:36

Ok, I've changed postgres version from alpine to 9.6, and in Dockerfile I have these lines:

RUN localedef -i pl_PL -c -f UTF-8 -A /usr/share/locale/locale.alias pl_PL.UTF-8
ENV LANG PL_PL.utf8
ENV LC_ALL="pl_PL.UTF-8"
ENV LC_CTYPE="pl_PL.UTF-8"

Now It's working and I have right order. But still I'am wondering how to build my postgres from docker-compose file.

Order Strings with polish in Postgres

1 Answers1