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
– pdem, Commented Jul 28, 2020 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
– Cezary Butler, Commented Jul 28, 2020 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
– danny, Commented Jul 28, 2020 at 14:53
So It is postgres related, just try the SQL query, maybe the locale is not installed, see this: stackoverflow.com/questions/18932922/… — pdem
– pdem, Commented Jul 28, 2020 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
– Robert Niestroj, Commented Jul 29, 2020 at 8:53

danny · Accepted Answer · 2020-08-03 13:36:24Z

2

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.

answered Aug 3, 2020 at 13:36

danny

6,0973 gold badges12 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Order Strings with polish in Postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related