I'm trying to use the Criteria Query trim() function to strip the leading zero's of a field in the database. The relevant part of the criteria query is:
{code:java}
final Expression<String> _code = cmm.get(CodeMasterMapping_.code);
builder.equal(builder.trim(Trimspec.LEADING, Character.valueOf('0'), _code), productCode)));
{code}
The SQL generated by this snippet is:
{code:sql}
trim(LEADING 0 from codemaster3_.comm_code)=?)
{code}
This leads to the following exception from Postgres:
{quote}PSQLException: ERROR: function pg_catalog.ltrim(character varying, integer) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.{quote}
Expected generated SQL:
{code:sql}
trim(LEADING '0' from codemaster3_.comm_code)=?)
{code}
Forum discussion: [Hibernate/JPA CriteriaQuery trim function wrong SQL Postgres|https://forum.hibernate.org/viewtopic.php?t=1043906]
SO Question: [http://stackoverflow.com/questions/41579182/does-hibernate-jpa-criteriaquery-trim-function-generate-the-wrong-sql-for-postgr] |
|