CODEWITHSUNDEEP
postgresql
Hardikgiri Goswami
Hardikgiri Goswami

Reputation: 514

Postgresql order by is not working properly

Hi Postgresql Experts,

stuck with order by of postgresql, as data is so sensitive pasted image here of pgAdmin.

enter image description here

As you can see in the image that "Les" coming first and "L." is coming after. Actually, "L." should come first and then "Les". "L." is coming in few columns too, but not sorted. So any idea why this happens?

This is the definition of table ( name column ):

CREATE TABLE public.res_partner
(
  id integer NOT NULL DEFAULT nextval('res_partner_id_seq'::regclass),
  name character varying,
  .....
  .....

Here is the index created for name column for this table.

CREATE INDEX res_partner_name_index
  ON public.res_partner
  USING btree
  (name COLLATE pg_catalog."default");

Thank you in advance for the help!

Upvotes: 2

Views: 2046

Answers (3)

Pavel Stehule
Pavel Stehule

Reputation: 45770

PostgreSQL uses libraries from operation system for local languages support. When you use ORDER BY clause without COLLATE clause, then Postgres uses default collate - you can show it by command SHOW lc_collate. The index is important just for speed-up of ORDER BY clause. It doesn't impact ordering. Some locales can use different ordering than you expect.

You can look on result:

SELECT datname, datcollate FROM pg_database;

If you expect different locale, then a) use COLLATE clause after ORDER BY, b) recreate database with different locale by dump/load.

Upvotes: 5

Laurenz Albe
Laurenz Albe

Reputation: 246288

I think you can solve this by using a different collation:

ORDER BY name COLLATE "C"

If you want the index to support this query, define it with the same collation.

Upvotes: 4

Abe
Abe

Reputation: 5508

Looking at your image, it appears that ORDER BY isn't taking periods or spaces into account, and goes directly to the next available letter. The cleanest way I can think of to handle this is:

  1. split name into three columns: first, middle, and last.
  2. Strip all periods and spaces.
  3. Order by last, first, middle.

Upvotes: 0

Related Questions