No operator matches the given name and argument type(s). You might need to add explicit type casts. — Netbeans, Postgresql 8.4 and Glassfish

33
votes

I am trying to edit a table in Postgresql using JPA in Glassfish using EclipseLink. When I insert an entity, it runs fine. But, when I try to edit or remove the same entity, it fails with the following error. Any idea?

Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.1.v20100213-r6600): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = character varying
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 38
Error Code: 0
        at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.processExceptionForCommError(DatabaseAccessor.java:1422)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:799)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:867)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:587)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:530)
        at org.eclipse.persistence.internal.sessions.AbstractSession.executeCall(AbstractSession.java:914)
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:205)
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:191)
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.deleteObject(DatasourceCallQueryMechanism.java:182)
        at org.eclipse.persistence.internal.queries.StatementQueryMechanism.deleteObject(StatementQueryMechanism.java:101)
        at org.eclipse.persistence.queries.DeleteObjectQuery.executeDatabaseQuery(DeleteObjectQuery.java:167)
        at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:675)
        at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:589)
        at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:109)
        at org.eclipse.persistence.queries.DeleteObjectQuery.executeInUnitOfWorkObjectLevelModifyQuery(DeleteObjectQuery.java:112)
        at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:86)
        at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2857)
        at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1225)
        at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1207)
        at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1167)
        at org.eclipse.persistence.internal.sessions.CommitManager.deleteAllObjects(CommitManager.java:297)
        at org.eclipse.persistence.internal.sessions.CommitManager.deleteAllObjects(CommitManager.java:256)
        at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1406)
        at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitToDatabase(RepeatableWriteUnitOfWork.java:547)
        at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1508)
        at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.issueSQLbeforeCompletion(UnitOfWorkImpl.java:3128)
        at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.issueSQLbeforeCompletion(RepeatableWriteUnitOfWork.java:268)
        at org.eclipse.persistence.transaction.AbstractSynchronizationListener.beforeCompletion(AbstractSynchronizationListener.java:157)
        at org.eclipse.persistence.transaction.JTASynchronizationListener.beforeCompletion(JTASynchronizationListener.java:68)
        at com.sun.enterprise.transaction.JavaEETransactionImpl.commit(JavaEETransactionImpl.java:412)
        ... 25 more
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = character varying
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 38
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:321)
        at com.sun.gjc.spi.base.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:108)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:792)
        ... 53 more
Java Result: 1
12
javapostgresqlormjpaeclipselink
Please show your entity, the corresponding table, maybe your code if relevant, and the generated SQL. See this previous question to output the generated SQL. – Pascal Thivent
To do not hack you ORM and external software postgres allow you register your own casts and compare operations. Please look example in similar question. P.S. It was the answer, but deleted by some reason… – Hubbitus

12 Answers

33
votes

I had this issue, and solved. This was due to the WHERE clause contains String value instead of integer value.

15
votes

This the main error:

ERROR: operator does not exist: integer = character varying

You code is trying to match an integer and a string, that's not going to work. Fix your code, get the query that is involved to see if you fixed it. See also the PostgreSQL log files.

A workaround (NOT A SOLUTION!) is to do some casting. Check this article.

5
votes

Doesn't look like you got an answer but this problem can also creep up if you're passing null ID's into your JPA Predicate.

For instance.

If I did a query on Cats to get back a list. Which returns 3 results.

List catList;

I then iterate over that List of cats and store a foriegn key of cat perhaps leashTypeId in another list.

List<Integer> leashTypeIds= new ArrayList<>();

for(Cats c : catList){
    leashTypeIds.add(c.getLeashTypeId);
}

jpaController().findLeashes(leashTypeIds);

If any of the Cats in catList have a null leashTypeId it will throw this error when you try to query your DB.

(Just realized I am posting on a 5 year old thread, perhaps someone will find this useful)

1
votes

Bro, I had the same problem. Thing is I built a query builder, quite an complex one that build his predicates dynamically pending on what parameters had been set and cached the queries. Anyways, before I built my query builder, I had a non object oriented procedural code build the same thing (except of course he didn't cache queries and use parameters) that worked flawless. Now when my builder tried to do the very same thing, my PostgreSQL threw this fucked up error that you received too. I examined my generated SQL code and found no errors. Strange indeed.

My search soon proved that it was one particular predicate in the WHERE clause that caused this error. Yet this predicate was built by code that looked like, well almost, exactly as how the procedural code looked like before this exception started to appear out of nowhere.

But I saw one thing I had done differently in my builder as opposed to what the procedural code did previously. It was the order of the predicates he put in the WHERE clause! So I started to move this predicate around and soon discovered that indeed the order of predicates had much to say. If I had this predicate all alone, my query worked (but returned an erroneous result-match of course), if I put him with just one or the other predicate it worked sometimes, didn't work other times. Moreover, mimicking the previous order of the procedural code didn't work either. What finally worked was to put this demonic predicate at the start of my WHERE clause, as the first predicate added! So again if I haven't made myself clear, the order my predicates where added to the WHERE method/clause was creating this exception.

1
votes

This is due to the mismatch of the data type of your java Entity and the database table column. Please review if all the column is exact same data type as your entity. This mismatch happens when we update our model attribute's data-type.

0
votes

I guess this can be due to many things. In my case it was having "WHERE id IN" condition in my query and I was setting IDs separated by dash as a string using setString method on PreparedStatement.

Not sure if there is better way to do this but I just added placeholder in my statement and replaced it by values on my own.

0
votes

If you are using Primefaces, you should insert inside the the .xhtml file so it converts correctly to java integer. For example:

<p:selectCheckboxMenu 
    id="frameSelect"
    widgetVar="frameSelectBox"
    filter="true"
    filterMatchMode="contains"
    label="#{messages['frame']}"
    value="#{platform.frameBean.selectedFramesTypesList}"
    converter="javax.faces.Integer">
    <f:selectItems
        value="#{platform.frameBean.framesTypesList}"
        var="area"
        itemLabel="#{area}"
        itemValue="#{area}" />
</p:selectCheckboxMenu>
0
votes

In query where you are passing query parameters, typecast parameter to an integer

e.g. in the case of PostgreSQL, it might be 

where table_name.column_name_with_integer_type = (:named_parameter_of_character_type)::integer

::integer will convert the parameter value into an integer.

0
votes

In my case, I used a keyword as a column name, which resulted in ERROR: operator does not exist: name = bigint

The solution was to use double quotes around the column name.

0
votes

I had this issue in a very simple DELETE statement, and it is now solved.

My issue was due to using backticks around the column (this column was named "id").

This query DID NOT WORK and resulted in "No operator matches the given name and argument type(s)"

DELETE FROM mytable WHERE `id` = 3      -- DO NOT USE BACKTICKS

Coming from mysql, in dynamic queries, I always `backtick` columns.

The following query DID WORK (with backticks removed):

DELETE FROM mytable WHERE id = 3
0
votes

I had this problem when i was trying to query by passing a Set and i didn't used In

example

problem : repository.findBySomeSetOfData(setOfData);

solution : repository.findBySomeSetOfDataIn(setOfData);

-1
votes

If anyone is having this exception and is building the query using Scala multi-line strings:

Looks like there is a problem with some JPA drivers in this situation. I'm not sure what is the character Scala uses for LINE END, but when you have a parameter right at the end of the line, the LINE END character seems to be attached to the parameter and so when the driver parses the query, this error comes up. A simple work around is to leave an empty space right after the param at the end:

SELECT * FROM some_table a
WHERE a.col = ?param
AND a.col2 = ?param2

So, just make sure to leave an empty space after param (and param2, if you have a line break there).