Postgres order by difference

user_5933369 0
Viewed 43

I am comparing query result set between PostgreSql 9.6 and PostgreSql 12 version. Noticed a very strange behavior in query result. Running below query from psql

SELECT current_database(),table_name
  FROM information_schema.tables
 WHERE table_type = 'BASE TABLE'
   AND table_schema NOT IN ('pg_catalog', 'information_schema') 
ORDER BY table_name;

PostgreSql 9.6 Output

current_database |          table_name          
------------------+------------------------------
mydb              |key_request_sum
mydb              |key_req_user

PostgreSql 12 output

current_database  |          table_name          
------------------+------------------------------    
mydb              |key_req_user
mydb              |key_request_sum

This is strange to see the different output for same query for different version. please suggest what to change in query to make Postgres 12 result same as 9.6

postgresql
1 Answers

If you want the 9.6 version, you can have it collate by the locality that worked in the days before UTF finally got fixed to the point of being usable:

create table key_req_user ();
create table key_request_user ();

select current_database(), table_name, pg_typeof(table_name)
  from information_schema.tables
 where table_type = 'BASE TABLE'
   and table_schema not in ('pg_catalog', 'information_schema')
 order by table_name collate "en_US.utf8";

db<>fiddle here

user_138083190
Related