1

we are using a PostgreSQL version 15 and have added 13 conversions from this blog entry: http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html

However, only now we have the problem when we want to join a string e.g. with a number:

SELECT 'Test: '||123.45 FROM TEST

The SQL returns the error here:

ERROR: operator is not unique: unknown || integer

If we remove the conversion "double percision --> text", the SQL works. However, we need the conversions elsewhere. We actually want to avoid using CAST(), ::VARCHAR or ::TEXT, as this affects a large number of queries.

Does anyone have any ideas on how we can solve the problem?

Improve this question

1 Answer 1

Reset to default
1

You have two options:

  1. change your statement to make clear that the first argument is a text:

    SELECT 'Test: '::text || 123.45

  2. drop the extra type casts and fix your SQL statements

I think that the second option is the better one.

Improve this answer
4
  • Option 2 cannot be implemented because the queries are used in an environment that includes several database types.
    – Athris
    Commented Nov 23, 2023 at 15:32
  • Pshaw. Don't tell me that other databases don't understand queries that use data types correctly. no database insists on you concatenating strings with numbers...
    – Laurenz Albe
    Commented Nov 23, 2023 at 15:38
  • Other database allow implicit cast. We are just introducing PostgreSQL and have now encountered the problem mentioned above.
    – Athris
    Commented Nov 23, 2023 at 15:47
  • I understand. But that doesn't rule out the second option. Being unambiguous about data types can actually prevent undesired behavior of the query. I can understand that the pain to fix all your SQL statements is great, but it might be the better option in the long run.
    – Laurenz Albe
    Commented Nov 23, 2023 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.