From rsk@zoniac.com Fri Aug 17 08:15:48 2012 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1T2Hie-0004gm-17 for pgsql-admin@postgresql.org; Fri, 17 Aug 2012 08:15:48 +0000 Received: from rs133.luxsci.com ([72.32.115.26]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1T2Hia-0001uK-D5 for pgsql-admin@postgresql.org; Fri, 17 Aug 2012 08:15:45 +0000 Received: from rsaravanakumar (NSG-Static-236.156.72.182.airtel.in [182.72.156.236] (may be forged)) (authenticated bits=0) by rs133.luxsci.com (8.13.8/8.13.8) with ESMTP id q7H8Fca2013279; Fri, 17 Aug 2012 03:15:40 -0500 Reply-To: From: "Saravanakumar Ramasamy" To: Cc: "'Gnanakumar'" , , Subject: When I executed type cast functions. The postgres normal concatenation operator query was breaking. Date: Fri, 17 Aug 2012 13:41:48 +0530 Organization: Zoniac Software Private Limited Message-ID: <000c01cd7c4f$f65e7ae0$e31b70a0$@com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000D_01CD7C7E.1016B6E0" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: Ac18T/LxSUY9gimMQ7aNUMPp8BOzIg== Content-Language: en-us X-Lux-Comment: Message q7H8Fca2013279 sent by user #78808 X-Pg-Spam-Score: 0.1 (/) X-Archive-Number: 201208/90 X-Sequence-Number: 39911 This is a multipart message in MIME format. ------=_NextPart_000_000D_01CD7C7E.1016B6E0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi All. Now I am using postgres 9.1.3 version . Before I am used 8.2.22 after I upgraded 9.1.3. Upgraded was successfully completed. In Postgres 8.2.22 #------------------# POSTGRES8222=# select TO_NUMBER('12345678',9999999999.99); to_number ----------- 12345678 (1 row) POSTGRES8222=# select a ||'$'|| b from test; ?column? ---------- 1$abcdef 2$ghijkl 3$3456 (3 rows) STATUS : Both queries are executed well. After upgrade In Postgres 9.1.3 #----------------------------------# Problem 1 : =========== beforetypecast=# select TO_NUMBER('12345678',9999999999.99); ERROR: function to_number(unknown, numeric) does not exist LINE 1: select TO_NUMBER('12345678',9999999999.99); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. EXCEPTION org.postgresql.util.PSQLException: ERROR: function to_number(numeric, numeric) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 150 Problem 2 : ============ beforetypecast=# select a ||'$'|| b from test; ?column? ---------- 1$abcdef 2$ghijkl 3$3456 (3 rows) ERROR : No I found solution. That solution is 13 implicit type cast. When I executed type cast functions. The postgres normal concatenation operator query was breaking. The error are follows Problem 1 : ============ aftertypecast=# select TO_NUMBER('12345678',9999999999.99); to_number ----------- 12345678 (1 row) ERROR : No Problem 2 : ============ aftertypecast=# select a ||'$'|| b from test; ERROR: operator is not unique: numeric || unknown LINE 1: select a ||'$'|| b from test; ^ HINT: Could not choose a best candidate operator. You might need to add explicit type casts. ********** Error ********** ERROR: operator is not unique: numeric || unknown SQL state: 42725 Hint: Could not choose a best candidate operator. You might need to add explicit type casts. Character: 10 With Regards, Saravanakumar Ramasamy. ------=_NextPart_000_000D_01CD7C7E.1016B6E0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi All.

Now I am using postgres 9.1.3 version . Before I am = used 8.2.22 after I upgraded 9.1.3. Upgraded was successfully completed. =

In Postgres 8.2.22

#------------------#

POSTGRES8222=3D# select = TO_NUMBER('12345678',9999999999.99);

to_number

-----------

12345678

(1 row)

POSTGRES8222=3D# select a ||'$'|| b from = test;

?column?

----------

1$abcdef

2$ghijkl

3$3456

(3 rows)

STATUS : Both queries are executed = well.

After upgrade In Postgres = 9.1.3

#----------------------------------#

Problem 1 :

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

beforetypecast=3D# select = TO_NUMBER('12345678',9999999999.99);

ERROR: function to_number(unknown, numeric) = does not exist

LINE 1: select = TO_NUMBER('12345678',9999999999.99);

&= nbsp; ^

HINT: No function matches the given name and = argument types. You might need to add explicit type casts.

EXCEPTION

org.postgresql.util.PSQLException: ERROR: function to_number(numeric, numeric) does not exist

Hint: No function matches the given name and = argument types. You might need to add explicit type casts.

Position: 150

Problem 2 :

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

beforetypecast=3D# select a ||'$'|| b from = test;

?column?

----------

1$abcdef

2$ghijkl

3$3456

(3 rows)

ERROR : No

I found solution. That solution is 13 implicit type = cast. When I executed type cast functions. The postgres normal concatenation = operator query was breaking. The error are follows

Problem 1 :

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

aftertypecast=3D# select = TO_NUMBER('12345678',9999999999.99);

to_number

-----------

12345678

(1 row)

ERROR : No

Problem 2 :

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

aftertypecast=3D# select a ||'$'|| b from = test;

ERROR: operator is not unique: numeric || = unknown

LINE 1: select a ||'$'|| b from = test;

&= nbsp; ^

HINT: Could not choose a best candidate = operator. You might need to add explicit type casts.

********** Error **********

ERROR: operator is not unique: numeric || = unknown

SQL state: 42725

Hint: Could not choose a best candidate operator. = You might need to add explicit type casts.

Character: 10

With Regards,

Saravanakumar Ramasamy.

------=_NextPart_000_000D_01CD7C7E.1016B6E0-- From Kevin.Grittner@wicourts.gov Mon Aug 20 16:08:58 2012 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1T3UXB-0004sG-VU for pgsql-admin@postgresql.org; Mon, 20 Aug 2012 16:08:58 +0000 Received: from gwmta.wicourts.gov ([165.219.244.99] helo=gw.wicourts.gov) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1T3UX9-00070K-JK for pgsql-admin@postgresql.org; Mon, 20 Aug 2012 16:08:57 +0000 Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Mon, 20 Aug 2012 11:08:53 -0500 Message-Id: <50321ABC0200002500049925@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.2 Date: Mon, 20 Aug 2012 11:08:44 -0500 From: "Kevin Grittner" To: , Cc: "'Gnanakumar'" , Subject: Re: When I executed type cast functions. The postgres normal concatenation operator query was breaking. References: <000c01cd7c4f$f65e7ae0$e31b70a0$@com> In-Reply-To: <000c01cd7c4f$f65e7ae0$e31b70a0$@com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Pg-Spam-Score: -4.4 (----) X-Archive-Number: 201208/98 X-Sequence-Number: 39919 "Saravanakumar Ramasamy" wrote: > Now I am using postgres 9.1.3 version . Before I am used 8.2.22 > ERROR: function to_number(unknown, numeric) does not exist > HINT: No function matches the given name and argument types. You > might need to add explicit type casts. > I found solution. That solution is 13 implicit type cast. When I > executed type cast functions [concatenations started breaking] > ERROR: operator is not unique: numeric || unknown > HINT: Could not choose a best candidate operator. You might need > to add explicit type casts. This was also posted on StackOverflow: http://stackoverflow.com/questions/12007988/when-i-executed-type-cast-functions-the-postgres-normal-concatenation-operator The accepted solution there was basically to drop the 13 implicit casts which were added in an attempt to maintain pre-8.3 behavior, and to change code which counted on the implicit casts which were eliminated in 8.3. -Kevin