Geek Answers Handbook

The execution of cast function types in postgresql 9.1.3 is not the same as in postgresql 8.2.22. Concatenation is not working properly

I used postgresql version 8.2.22, then I upgraded to postgresql 9.1.3 and the update completed successfully.

But now some videos do not work as before!

In Postgres 8.2.22

I run these two queries and they both work correctly:

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)

After upgrading to Postgres 9.1.3

I run the same queries and now they throw errors:

 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 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

Why does casting in postgresql not work as before?

+4
source share
1 answer

Starting with PostgreSQL 8.3, the number of automatic throws is less. This has been changed for two reasons:

  • Many new high-performance types are being introduced, and automatic casting prevents them from using literals in such a way that "first-class" types can use them. The narrowing of cases when the parser tried to guess the data type allowed new types that the user could plug in to use them in a more natural way.

  • Numerous error messages turned out to be people who accidentally receive "benefits" from the automatic cast when they did not know that this was happening, which makes it difficult to find errors in their coding. After 8.3 was released, there were about the same number of people that they said that as a result of the change, hidden errors were found in their own code, since there were people complaining that their code now needed that he had not done before.

It looks like you tried to "solve" this "problem" by adding implicit type conversions. This is a minefield; I do not recommend. You will limit what functions you can safely use, and you will not have the end of dodgy little mistakes that no one makes, and no one can handle it. It’s better to fix your code to prevent so many implicit conversions.

One thing that can confuse you is that in PostgreSQL '1.2' not a string literal. This is a literal unknown type:

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

PostgreSQL dwells on resolving the type of literal as much as possible, which works great with all of these new data types that I described. In extreme cases, if the time comes when he must decide the type and there are no other clues, he considers it as a 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" fails, because with all the implicit types you added, there is more than one possible || which can be selected depending on what implicit techniques they performed, and there was no principled way to choose among them. If you change this line to the following, it should work again:

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

I argue that it would be cleaner and safer not to add these implicit throws and change the first problem code from:

 select TO_NUMBER('12345678',9999999999.99);

in

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

In the end, this second parameter is a format string, not a number. You cannot omit the quotation if you want to do something like:

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

More articles:


All Articles