postgresql - Sorting order behaviour between Postgres and Mysql -

postgresql - Sorting order behaviour between Postgres and Mysql -

i have faced unusual sort order behaviour between postgres & mysql.

for example, have created simple table varchar column , inserted 2 records below in both postgres , mysql.

create table mytable(name varchar(100)); insert mytable values ('aaaa'), ('aa_a');

now, have executed simple select query order column.

postgres sort order:

test=# select * mytable order (name) asc; name ------ aa_a aaaa (2 rows)

mysql sort order:

mysql> select * mytable order name asc; +------+ | name | +------+ | aaaa | | aa_a | +------+ 2 rows in set (0.00 sec)

postgres , mysql both returning same records different order.

my question 1 correct?

how results in same order in both database?

edited:

i tried query order collate, solved problem.

tried this

mysql> select * t order name collate utf8_bin; +------+ | name | +------+ | aa_a | | aaaa | +------+ 3 rows in set (0.00 sec)

thanks.

there no "correct" way sort data.

you need read on "locales".

different locales provide (among other things) different sort orders. might have database using iso-8859-1 or utf-8 can represent several different languages. rules sorting english language different french or german.

postgresql uses underlying operating-system's back upwards locales, , not locales available on platforms. alternative provide own support, can have incompatibilities within 1 machine.

i believe mysql takes sec option, i'm no expert on mysql.

mysql postgresql