PostgreSQL sort prioritises whitespace differently to to Mongo and MySQL

Question

I was comparing the speeds of several databases, working with some randomly generated strings and curiously noticed the sorting differed. I am working with thousands of rows but have isolated my data to just these 2 strings for simplicity:

Pazyn Qhhbltw Vxsnwgt
Pazynkfc Tttzqjss Zzpxuarhy

Mongo and MySQL both sort them in the order displayed above, but Postgres switches them around. It seems that the space character is considered to be before "k" by both Mongo and MySQL but after it by Postgres.

How can I get Postgres to fall-in and be consistent with MySQL and Mongo?

I am using Postgres version 10.10 and MySQL 8.0.18.

Both columns are varchar(32) with no specific collation specified so I presume they are using default.

I've tried both with and without an index and I've tried several types of collation on the index but still get the same result.

I'm not sure how to debug this.

nbk · Accepted Answer · 2020-04-21 15:43:11Z

2

Use bytes order

ORDER BY texta::bytea;

CREATE TABLE  temp (
id INTEGER 
,texta varchar(50)
)

✓

INSERT INTO temp VALUES (1,'Pazyn Qhhbltw Vxsnwgt'),
(2,'Pazynkfc Tttzqjss Zzpxuarhy')

2 rows affected

SELECT * FROM temp ORDER BY texta;

id | texta                      
-: | :--------------------------
 2 | Pazynkfc Tttzqjss Zzpxuarhy
 1 | Pazyn Qhhbltw Vxsnwgt

SELECT * FROM temp ORDER BY texta::bytea;

id | texta                      
-: | :--------------------------
 1 | Pazyn Qhhbltw Vxsnwgt      
 2 | Pazynkfc Tttzqjss Zzpxuarhy

SELECT * FROM temp;

id | texta                      
-: | :--------------------------
 1 | Pazyn Qhhbltw Vxsnwgt      
 2 | Pazynkfc Tttzqjss Zzpxuarhy

db<>fiddle here

answered Apr 21, 2020 at 15:43

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

3 Comments

Martin Joiner Over a year ago

Wow that was quick. I'm new to Postgres so tell me, how did you know to use Bytes order? Is this a common thing that all postgressionists (I presume that's how you refer to them) know to consider when sorting? Or is this deep under the bonnet stuff?

nbk Over a year ago

it is a frequent question from users that change from mysql. As you have to learn more about postgre you learn how it works under the hood, but that that you have to do with every sql language

Martin Joiner Over a year ago

Yup true. As I compare dbs I am factoring out my own bias from 20 years experience playing around with MySQL. Thanks for explaining.

Collectives™ on Stack Overflow

PostgreSQL sort prioritises whitespace differently to to Mongo and MySQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related