Postgres Order By: the resultset is sorted differently

When querying different databases of Postgres the resultset is ordered differently. It happens because of differences in the datcollate and datctype.

For example:

Postgres DB Server #1

select datcollate, datctype from pg_database where datname='<my_db>';

         datcollate         |          datctype          
----------------------------+----------------------------
 English_United States.1252 | English_United States.1252

Postgres DB Server #2

select datcollate, datctype from pg_database where datname='<my_db>';

 datcollate |  datctype  
------------+------------
 en_US.utf8 | en_US.utf8

In case names contain both capital and small letters, the resultset will be ordered differently. For example, when querying the following:

SELECT table_name, column_name, data_type, is_nullable, character_maximum_length, column_default
FROM information_schema.columns
WHERE table_schema = 'public' and table_name != 'pg_stat_statements'
ORDER BY table_name, ordinal_position;

Postgres DB Server #1

...
dmitrYtest
dmitry
...

Postgres DB Server #2

...
dmitry
dmitrYtest
...

Blog

Postgres Order By: the resultset is sorted differently

Dmitry Romanoff

Join Our Newsletter. No Spam, Only the good stuff.

Related