DEV Community

Dmitry Romanoff
Dmitry Romanoff

Posted on • Updated on

Postgres Order By: the resultset is sorted differently

#postgres #database #devops #sql

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
Enter fullscreen mode Exit fullscreen mode

Postgres DB Server #2

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

 datcollate |  datctype  
------------+------------
 en_US.utf8 | en_US.utf8
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Postgres DB Server #1

...
dmitrYtest
dmitry
...
Enter fullscreen mode Exit fullscreen mode

Postgres DB Server #2

...
dmitry
dmitrYtest
...
Enter fullscreen mode Exit fullscreen mode

Top comments (0)

Read next

esproc_spl profile image

How to Calculate Median on Values That Have Corresponding Quantities

Judy -

dariusc16 profile image

Express

Darius Cooper -

allenheltondev profile image

Serverless Postgres with Neon - My first impression

Allen Helton -

itsjjpowell profile image

Passing The DataCamp SQL Associate Certificate

Jonathan Powell -