agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedUPPER() Function Not Working as Expected in PostgreSQL 12.5 Version
3+ messages / 3 participants
[nested] [flat]
* UPPER() Function Not Working as Expected in PostgreSQL 12.5 Version
@ 2021-07-07 17:35 Saraswat, Dhruv <dhruv.saraswat@thermofisher.com>
0 siblings, 1 reply; 3+ messages in thread
From: Saraswat, Dhruv @ 2021-07-07 17:35 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>; +Cc: Perumalla, Vijaya S. <Vijaya.Perumalla@thermofisher.com>; AR, KarthikRaja <karthikraja.ar@thermofisher.com>; bhorhari, vikas <vikas.bhorhari@thermofisher.com>; SV, Sushanth <sushanth.sv@thermofisher.com>; Dahiya, Rohit <rohit.dahiya@thermofisher.com>; Thota, DurgaKumari <Durga.Kumari@thermofisher.com>; Venkatachalam, Sundarrajan <Sundarrajan.Venkatachalam@thermofisher.com>; Appandarajan, Vinoth <vinoth.a@thermofisher.com>; Barchhiha, Vaibhav <vaibhav.barchhiha@thermofisher.com>; Prajapati, Priya <priya.prajapati@thermofisher.com>; Kumar, Ravi <ravi.kumar@thermofisher.com>; Sarkar, Madhurima <madhurima.sarkar@thermofisher.com>
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<https://www.postgresql.org/docs/current/functions-string.html; 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.
________________________________
This e-mail and any attachments thereto may contain confidential information and/or information protected by intellectual property rights for the exclusive attention of the intended addressees named above. If you have received this transmission in error, please immediately notify the sender by return e-mail and delete this message and its attachments permanently from your system/server, if stored. Unauthorized use, copying or further full or partial distribution of this e-mail or its contents is prohibited and will be subject to prosecution.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: UPPER() Function Not Working as Expected in PostgreSQL 12.5 Version
@ 2021-07-08 16:10 David G. Johnston <david.g.johnston@gmail.com>
parent: Saraswat, Dhruv <dhruv.saraswat@thermofisher.com>
0 siblings, 1 reply; 3+ messages in thread
From: David G. Johnston @ 2021-07-08 16:10 UTC (permalink / raw)
To: Saraswat, Dhruv <dhruv.saraswat@thermofisher.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>; Perumalla, Vijaya S. <Vijaya.Perumalla@thermofisher.com>; AR, KarthikRaja <karthikraja.ar@thermofisher.com>; bhorhari, vikas <vikas.bhorhari@thermofisher.com>; SV, Sushanth <sushanth.sv@thermofisher.com>; Dahiya, Rohit <rohit.dahiya@thermofisher.com>; Thota, DurgaKumari <Durga.Kumari@thermofisher.com>; Venkatachalam, Sundarrajan <Sundarrajan.Venkatachalam@thermofisher.com>; Appandarajan, Vinoth <vinoth.a@thermofisher.com>; Barchhiha, Vaibhav <vaibhav.barchhiha@thermofisher.com>; Prajapati, Priya <priya.prajapati@thermofisher.com>; Kumar, Ravi <ravi.kumar@thermofisher.com>; Sarkar, Madhurima <madhurima.sarkar@thermofisher.com>
On Thu, Jul 8, 2021 at 2:26 AM Saraswat, Dhruv <
dhruv.saraswat@thermofisher.com> wrote:
> *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
>
I unfortunately don't know the answer to your primary query but you are
definitely misusing the "name" datatype. An email address should be stored
using text. In fact, per the documentation, user schemas should not use
the "name" type for any reason.
In furtherance of diagnosing the observed problem, though, showing the
explain analyze plans from the two systems would be helpful - mostly in
trying to figure out whether indexes use is a differentiating factor.
Being able to get rid of the table altogether would be a nice bonus -
construct cases with working and non-working values (lower and upper
results) looking for observed differences.
In short, though, while their may be a bug here, and we'd like to know, the
suggested course of action for you is to fix your schema to use the
documented text data type and get rid of the effectively undocumented
"name".
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: UPPER() Function Not Working as Expected in PostgreSQL 12.5 Version
@ 2021-07-08 16:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2021-07-08 16:49 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Saraswat, Dhruv <dhruv.saraswat@thermofisher.com>; pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>; Perumalla, Vijaya S. <Vijaya.Perumalla@thermofisher.com>; AR, KarthikRaja <karthikraja.ar@thermofisher.com>; bhorhari, vikas <vikas.bhorhari@thermofisher.com>; SV, Sushanth <sushanth.sv@thermofisher.com>; Dahiya, Rohit <rohit.dahiya@thermofisher.com>; Thota, DurgaKumari <Durga.Kumari@thermofisher.com>; Venkatachalam, Sundarrajan <Sundarrajan.Venkatachalam@thermofisher.com>; Appandarajan, Vinoth <vinoth.a@thermofisher.com>; Barchhiha, Vaibhav <vaibhav.barchhiha@thermofisher.com>; Prajapati, Priya <priya.prajapati@thermofisher.com>; Kumar, Ravi <ravi.kumar@thermofisher.com>; Sarkar, Madhurima <madhurima.sarkar@thermofisher.com>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Jul 8, 2021 at 2:26 AM Saraswat, Dhruv <
> dhruv.saraswat@thermofisher.com> wrote:
>> 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
> I unfortunately don't know the answer to your primary query but you are
> definitely misusing the "name" datatype.
Indeed. I suspect that this was done as some sort of substitute for
declaring the column with collation "C", and the reason it's acting
differently in v12 is related to the fact that name columns are no
longer forced to behave as though they have collation "C". We don't
have enough details to be sure of that though.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-07-08 16:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-07-07 17:35 UPPER() Function Not Working as Expected in PostgreSQL 12.5 Version Saraswat, Dhruv <dhruv.saraswat@thermofisher.com>
2021-07-08 16:10 ` David G. Johnston <david.g.johnston@gmail.com>
2021-07-08 16:49 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox