CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
kurtgn
kurtgn

Reputation: 8710

postgres doesn't sort properly

This is my flask.SQLAlchemy query:

available_subservices = (
    ServicesForOffer.query
    .filter_by(type=u'Дополнительная')
    .order_by(ServicesForOffer.name)
    .all()
)

so I print it out:

for subs in available_subservices:
    print subs.name

and what I see is:

Доп.створка
Окно с двойными рамами (2 створки)
Окно с двойными рамами (3 створки)
окно с балконной дверью
Эркер
Мойка окон
Мойка окон, уборка кухни, уборка ванной и сан.узла
мытье холодильника
балкон
глажка
дорога
Разбор шкафа/антресоли/гардеробной
Мелкая бытовая техника
Жалюзи вертикальные
духовка

I don't expect anyone to know Cyrillic alphabet order, but still you can see that capitals are scattered all over, and дорога and духовка are not close to each other. Some kind of Python2 & Unicode issues probably?

P.S. when I sort by ServicesForOffer.name.desc() the same weird order is reproduced, inversed of course. So there's clearly something Postgres knows that I don't.

Upvotes: 1

Views: 364

Answers (2)

wanderlust
wanderlust

Reputation: 1936

I had the simmilar problem with MySQL sorting. And the solution was:

  • to set DEFAULT CHARACTER SET to schema to 'utf-8' (default was latin-1) and
  • to set DEFAULT COLLATE to schema to 'utf8_unicode_ci'.

Another way is to create correct schema in your database.

Upvotes: 1

Vyktor
Vyktor

Reputation: 20997

You probably don't have collation set properly which is responsible for how strings are sorted:

The collation feature allows specifying the sort order and character classification behavior of data per-column, or even per-operation. This alleviates the restriction that the LC_COLLATE and LC_CTYPE settings of a database cannot be changed after its creation.

You may try this by simply running the query:

schema=> SELECT 'дорога' > 'духовка' COLLATE "en_US";
 ?column?
----------
 f
(1 row)

Or executing your query manually in PostgreSQL shell with:

SELECT ...
FROM ...
WHERE ...
ORDER BY name COLLATE "your collation"

If this works for you, you can just altering columns collation:

ALTER [ COLUMN ] column [ SET DATA ] TYPE data_type [ COLLATE collation ]
      [ USING expression ]

Upvotes: 2

Related Questions