Well that depends on exactly what you are looking for.
If you mean in terms of the TCK, then there really is no workaround; we will always have this failure on PostgreSQL (perhaps until 8.4 according to Tom Lane).
If you mean from an end user perspective, this falls into the category of writing "portable" HQL/JPA-QL; basically: chose an escape character that's not problematic on any database.
Steve Ebersole [15/Jun/07 06:02 PM]
Well that depends on exactly what you are looking for.
If you mean in terms of the TCK, then there really is no workaround; we will always have this failure on PostgreSQL (perhaps until 8.4 according to Tom Lane).
If you mean from an end user perspective, this falls into the category of writing "portable" HQL/JPA-QL; basically: chose an escape character that's not problematic on any database.
No Steve, we want a paragraph to add in the release notes to warn a customer about this problem with escape syntax in LIKE clauses, in case they try and do that with a PGSQL 8.2. Something like 1) what they should avoid 2) an alternative way of doing it. Perhaps something from your discussions with Tom?
Fernando Nasser [18/Jun/07 10:42 AM]
No Steve, we want a paragraph to add in the release notes to warn a customer about this problem with escape syntax in LIKE clauses, in case they try and do that with a PGSQL 8.2. Something like 1) what they should avoid 2) an alternative way of doing it. Perhaps something from your discussions with Tom?
There are certain "escape characters" you'll want to avoid in the interest of portability amongst different database vendors. The back-slash ('\'), for example, has special meaning on PostgreSQL (and possible other databases as well) and cannot be used in an escape sequence as it can on other databases. Instead, chose an escape character that is reasonably safe on all databases, like a "pipe" ('|').
Steve Ebersole [18/Jun/07 05:29 PM]
As we discussed on irc this morning...
There are certain "escape characters" you'll want to avoid in the interest of portability amongst different database vendors. The back-slash ('\'), for example, has special meaning on PostgreSQL (and possible other databases as well) and cannot be used in an escape sequence as it can on other databases. Instead, chose an escape character that is reasonably safe on all databases, like a "pipe" ('|').
retried this test avoiding the use of the backslash as the escape character and the query worked as expected. Closing as this was what the release note suggested with PostgresSQL.
Shelly McGowan [30/Aug/07 04:53 PM]
retried this test avoiding the use of the backslash as the escape character and the query worked as expected. Closing as this was what the release note suggested with PostgresSQL.
Bug
Closed
Major
Query using Escape syntax in LIKE expression with PostgresSQL 8.2 results in PSQLException: The column index is out of range 