Re:

I got the same 144 rows here.

Have dropped the test database and recreated it again, then performed
these SQL commands:

select 'test'||1;

CREATE FUNCTION pg_catalog.text(integer) RETURNS text STRICT IMMUTABLE
LANGUAGE SQL AS 'SELECT textin(int4out($1));';
CREATE CAST (integer AS text) WITH FUNCTION pg_catalog.text(integer) AS
IMPLICIT;

select 'test'||1;

The first select worked but the second got the error.
ERROR: operator is not unique: unknown || integer
LINE 1: select 'teste'||1;
^
HINT: Could not choose a best candidate operator. You might need to add
explicit type casts.

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

ERROR: operator is not unique: unknown || integer
SQL state: 42725
Hint: Could not choose a best candidate operator. You might need to add
explicit type casts.
Character: 15

On Wed, May 15, 2013 at 6:02 PM, AI Rumman <rummandba(at)gmail(dot)com> wrote:

> I am using the same version and its fine.
> postgres=# select version();
> version
>
>
> ---------------------------------------------------------------------------------------------------------------
> PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2
> 20080704 (Red Hat 4.1.2-52), 64-bit
>
> Try out the query:
>
> SELECT ct.*,
> source_t.typname as source_type
> ,target_t.typname as target_type
> , proc.proname
> FROM
> pg_cast as ct
> , pg_type as source_t
> , pg_type as target_t
> ,pg_proc as proc
> WHERE
> ct.castsource = source_t.oid
> and ct.casttarget = target_t.oid
> and ct.castfunc = proc.oid
>
> I get 144 rows.
> http://www.rummandba.com/2013/02/postgresql-type-casting-information.html
>
>
>
>
> On Wed, May 15, 2013 at 4:54 PM, Carlos Henrique Reimer <
> carlos(dot)reimer(at)opendb(dot)com(dot)br> wrote:
>
>> It works if I drop the functions but then the select trim(1) does not
>> work;
>>
>>
>> On Wed, May 15, 2013 at 5:38 PM, AI Rumman <rummandba(at)gmail(dot)com> wrote:
>>
>>> Drop those functions and try again.
>>>
>>>
>>> On Wed, May 15, 2013 at 4:22 PM, Carlos Henrique Reimer <
>>> carlos(dot)reimer(at)opendb(dot)com(dot)br> wrote:
>>>
>>>> The PG version I'm using is 9.2.4.
>>>>
>>>> It works but after recreating the implicit cast with the function:
>>>>
>>>>
>>>> CREATE FUNCTION pg_catalog.text(integer) RETURNS text STRICT IMMUTABLE
>>>> LANGUAGE SQL AS 'SELECT textin(int4out($1));';
>>>> CREATE CAST (integer AS text) WITH FUNCTION pg_catalog.text(integer) AS
>>>> IMPLICIT;
>>>>
>>>> it does not work anymore, even using the explicit cast:
>>>>
>>>> select 'teste'||1::int;
>>>>
>>>> ERROR: operator is not unique: unknown || integer
>>>> LINE 1: select 'teste'||1::int;
>>>>
>>>> ^
>>>> HINT: Could not choose a best candidate operator. You might need to
>>>> add explicit type casts.
>>>>
>>>>
>>>> ********** Error **********
>>>>
>>>> ERROR: operator is not unique: unknown || integer
>>>> SQL state: 42725
>>>> Hint: Could not choose a best candidate operator. You might need to add
>>>> explicit type casts.
>>>> Character: 15
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> On Wed, May 15, 2013 at 4:45 PM, AI Rumman <rummandba(at)gmail(dot)com> wrote:
>>>>
>>>>> Ah! I missed that.
>>>>> Which version of 9.2 you are working with? I am also at 9.2 and its
>>>>> working fine.
>>>>> Try out using
>>>>> select 'teste'||1::int;
>>>>>
>>>>> See if it works or not.
>>>>>
>>>>>
>>>>> On Wed, May 15, 2013 at 3:41 PM, Carlos Henrique Reimer <
>>>>> carlos(dot)reimer(at)opendb(dot)com(dot)br> wrote:
>>>>>
>>>>>> Actually, as stated in my first note, this is what I've done to fix
>>>>>> the select 2 (select trim(1);) but after creating this function/cast the
>>>>>> select 1 (select 'teste'||1;) started not working.
>>>>>>
>>>>>>
>>>>>> On Wed, May 15, 2013 at 4:24 PM, AI Rumman <rummandba(at)gmail(dot)com>wrote:
>>>>>>
>>>>>>> Create the functions:
>>>>>>> CREATE FUNCTION pg_catalog.text(integer) RETURNS text STRICT
>>>>>>> IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int4out($1));';
>>>>>>> CREATE CAST (integer AS text) WITH FUNCTION pg_catalog.text(integer)
>>>>>>> AS IMPLICIT;
>>>>>>>
>>>>>>> http://www.rummandba.com/2010/10/migrate-database-from-postgresql-81-to.html
>>>>>>>
>>>>>>> It'll work.
>>>>>>>
>>>>>>>
>>>>>>> On Wed, May 15, 2013 at 3:17 PM, Carlos Henrique Reimer <
>>>>>>> carlos(dot)reimer(at)opendb(dot)com(dot)br> wrote:
>>>>>>>
>>>>>>>> Hi,
>>>>>>>>
>>>>>>>> Currently, our application is still using PG 8.2 and we are trying
>>>>>>>> to use 9.2 but there are some problems related with the implicit casts
>>>>>>>> removed on 8.3.
>>>>>>>>
>>>>>>>> Example:
>>>>>>>>
>>>>>>>> 1) select 'teste'||1;
>>>>>>>> 2) select trim(1);
>>>>>>>>
>>>>>>>> Select 1 & 2 does run fine on 8.2 but in 9.2 select 1 is ok and
>>>>>>>> select 2 got an error due the fact implicit cast were removed on PG 8.3:
>>>>>>>>
>>>>>>>> ERROR: function pg_catalog.btrim(integer) does not exist
>>>>>>>> LINE 1: select trim(1);
>>>>>>>> ^
>>>>>>>> HINT: No function matches the given name and argument types. You
>>>>>>>> might need to add explicit type casts.
>>>>>>>>
>>>>>>>> ********** Error **********
>>>>>>>>
>>>>>>>> ERROR: function pg_catalog.btrim(integer) does not exist
>>>>>>>> SQL state: 42883
>>>>>>>> Hint: No function matches the given name and argument types. You
>>>>>>>> might need to add explicit type casts.
>>>>>>>> Character: 8
>>>>>>>>
>>>>>>>> Recreated the implicit cast for select 2:
>>>>>>>>
>>>>>>>> CREATE FUNCTION pg_catalog.text(integer) RETURNS text STRICT
>>>>>>>> IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int4out($1));';
>>>>>>>> CREATE CAST (integer AS text) WITH FUNCTION
>>>>>>>> pg_catalog.text(integer) AS IMPLICIT;
>>>>>>>>
>>>>>>>> Now select 2 works fine but select 1 gets an error:
>>>>>>>>
>>>>>>>> ERROR: operator is not unique: unknown || integer
>>>>>>>> LINE 1: select 'teste'||1;
>>>>>>>> ^
>>>>>>>> HINT: Could not choose a best candidate operator. You might need
>>>>>>>> to add explicit type casts.
>>>>>>>>
>>>>>>>> ********** Error **********
>>>>>>>>
>>>>>>>> ERROR: operator is not unique: unknown || integer
>>>>>>>> SQL state: 42725
>>>>>>>> Hint: Could not choose a best candidate operator. You might need to
>>>>>>>> add explicit type casts.
>>>>>>>> Character: 15
>>>>>>>>
>>>>>>>>
>>>>>>>> Is there a way to make both selects work on PG 9.2 without explicit
>>>>>>>> casts?
>>>>>>>>
>>>>>>>> Thank you in advace!
>>>>>>>>
>>>>>>>> --
>>>>>>>> Reimer
>>>>>>>> 47-3347-1724 47-9183-0547 msn: carlos(dot)reimer(at)opendb(dot)com(dot)br
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Reimer
>>>>>> 47-3347-1724 47-9183-0547 msn: carlos(dot)reimer(at)opendb(dot)com(dot)br
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Reimer
>>>> 47-3347-1724 47-9183-0547 msn: carlos(dot)reimer(at)opendb(dot)com(dot)br
>>>>
>>>
>>>
>>
>>
>> --
>> Reimer
>> 47-3347-1724 47-9183-0547 msn: carlos(dot)reimer(at)opendb(dot)com(dot)br
>>
>
>

--
Reimer
47-3347-1724 47-9183-0547 msn: carlos(dot)reimer(at)opendb(dot)com(dot)br