Postgresql order by is not working properly

Question

Hi Postgresql Experts,

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

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!

Pavel Stehule · Accepted Answer · 2019-06-26 19:05:45Z

5

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.

answered Jun 26, 2019 at 19:05

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

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

1 Comment

Hardikgiri Goswami Over a year ago

Very nice explanation Mr. Pavel Stehule. Thank you for your answer it solved my issue and it gave me very useful knowledge.

Laurenz Albe · Accepted Answer · 2019-06-26 19:05:09Z

4

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.

answered Jun 26, 2019 at 19:05

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Comments

Abe · Accepted Answer · 2019-06-26 19:03:00Z

0

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:

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

answered Jun 26, 2019 at 19:03

Abe

5,6082 gold badges17 silver badges28 bronze badges

Collectives™ on Stack Overflow

Postgresql order by is not working properly

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related