Executing type cast functions in postgresql 9.1.3 is not the same as in postgresql 8.2.22. Concatenation is not working correctly

Beginning with PostgreSQL 8.3 there are fewer automatic casts. This was changed for two reasons:

1. Many new high-powered types were being introduced, and automatic casting prevented these from being able to use literals in a way that "first-class" types could. Narrowing the cases where the parser tried to guess a data type allowed new types that a user could plug in to be used in a more natural way.

2. Numerous bug reports turned out to be people accidentally getting the "benefit" of automatic casting when they didn't recognize that it was happening, making it hard to find their application coding errors. After 8.3 was released, there were roughly the same number of people posting to say that the change uncovered hidden bugs in their own code as there were people complaining that their code now needed casts where it previously didn't.

It appears that you tried to "solve" this "problem" by adding implicit typecasts. This is a minefield; I don't recommend it. You will limit what features you can safely use and you will have no end of quirky little bugs that nobody else does, and nobody can easily help you with. It is better to fix your code not to assume so many implicit conversions.

One thing which may be confusing you is that in PostgreSQL, '1.2' is not a string literal. It is a literal of unknown type:

test=# select pg_typeof('1.2');
 pg_typeof 
-----------
 unknown
(1 row)

PostgreSQL holds off on resolving the type of a literal as long as it can, which works great with all those new data types I was describing. As a last resort, if the time comes when it has to resolve the type and there are no other clues, it treats it as type text.

test=# select pg_typeof(case when true then '1.2' else null end);
 pg_typeof 
-----------
 text
(1 row)

test=# select pg_typeof(case when true then '1.2' else 2.3 end);
 pg_typeof 
-----------
 numeric
(1 row)

Problem 2, "aftertypecast", is failing because with all the implicit type casts you added, there is more than one possible operator || which could be chosen depending on which implicit typecasts it performed, and there was no principled way to choose among them. If you change that line to the following, it should work again:

select a || '$'::text || b from test;

I'm arguing it would be cleaner and safer not to add those implicit casts and change the first problem code from:

select TO_NUMBER('12345678',9999999999.99);

to:

select TO_NUMBER('12345678', '9999999999.99');

After all, that second parameter is a format string, not a number. You can't omit the quoting if you want to do something like:

test=# select to_number('12,454.8-', '99G999D9S');
 to_number 
-----------
  -12454.8
(1 row)