Обсуждение: UPPER() Function Not Working as Expected in PostgreSQL 12.5 Version

Hello PostgreSQL Team,

We request your help with an issue which we found while we were working with our application which interacts with an AWS RDS Instance running on a PostgreSQL 12.5 Engine.

Short Version –

The PostgreSQL UPPER() function was working fine in PostgreSQL 9.6.11, however the UPPER() function does not work as expected in PostgreSQL 12.5 for some inputs.

Long Version –

We have a table in which 1 of the columns is called ‘name’ with a datatype of ‘name’. This column stores username values, for example ==> dhruv.saraswat@thermofisher.com

The following query gives no result (0 rows in the result) in PostgreSQL 12.5 for only some ‘name’ values –

SELECT * FROM <table_name> WHERE UPPER(name)=UPPER('<some_name_which_is_present_in_the_table>');

However, replacing UPPER with LOWER in the above query returns the expected row in the result in PostgreSQL 12.5 –

SELECT * FROM <table_name> WHERE LOWER(name)=LOWER('<some_name_which_is_present_in_the_table>');

Both the queries mentioned above return the expected result in PostgreSQL 9.6.11. We encountered this issue after upgrading the PostgreSQL version of our AWS RDS Instance from 9.6.11 to 12.5. The upgrade process involved –

• Taking a snapshot of the 9.6.11 RDS Instance
• Upgrading the snapshot version from 9.6.11 to 12.5, and then
• Creating a new RDS Instance from this newly-created-and-upgraded snapshot.

This meant that the table structure, database locale (en_US.UTF-8) and data remained unchanged between both the 9.6.11 and 12.5 AWS RDS Instances.

Please let us know if there is any other information needed from our side.

Thank you for your time,

Dhruv Saraswat.