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

Question

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

Voracious

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. – Zaid

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… – Catnip

Answer 1

This issue was due to the WHERE clause containing a String value instead of an integer value.

Proud

Answer 2

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.

Spavined

did you find what was throwing this up? – Hulse

A workaround is a solution. What's the difference at the end of the day ? – Spirit

Answer 3

This is due to the mismatch column type to the Java entity data type. 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.

Breedlove

Answer 4

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)

Selassie

and still useful after 6 more years – Navvy

What if I want to pass null? What if my search parameter is sometimes empty and I still want the query to execute and give me a resultset? I am facing this situation and I am not able to solve it. – Dalliance

It would depend on how your controller is constructing the query. However in this case it would be some type of IN operator for instance Select * from cats where leashTypeId IN (ids) In this case you can't pass null. If you wanted to pass null you would need a different type of query and I don't think you would be passing a list. – Selassie

Answer 5

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.

Lychnis

Answer 6

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.

Scandian

Answer 7

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

Gingham

Answer 8

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.

Burgos

Answer 9

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

Copulative

Answer 10

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.

Conduction

Answer 11

This is due to null value which you are passing in JPA Query.

I tried every method but didn't worked for me. But you can trick JPA in case of postgres by given below code.

Java Service Code :

You can add verification types according to your input like: verificationTypes.add(2); verificationTypes.add(1);

List<Integer> verificationTypes = new ArrayList<>();
Integer status = 1;
if(Objects.nonNull(verificationTypes) && verificationTypes.size()>0) {
    status = 0;
}
empRepo.findAllEmp(verificationTypes,status);

JPA Repository code :

@Query(value = "select * from emp where (case when :status = 1 then true else verf_stat in (:verificationTypes) end ) ", nativeQuery = true)
List<Employee> findAllEmp(List<Integer> verificationTypes,Integer status);

Koren

Answer 12

This happens to me with PostgreSQL and JPA. However it was purely PostgreSQL error and has happened when providing a string value for a parameter in a query that expects it in defined custom type (eg. You can define your own enumeration type in PostgresSQL). The problem got solved by explicit type casting.

Eg. The column delivery_package_type is a type of package_types, which is an enumeration has different values (NORMAL, TYPE_A, TYPE_B). When comparing it in SQL queries with string values provided it needs to be casted to string.

select id, delivery_date, description from delivered_package where cast(package_type as text) = ? 

Here the parameter provided is string type.

Earth

Answer 13

Add a parameter in the connection URL: stringtype=unspecified.

Example: jdbc:postgresql://localhost:5432/databaseName?stringtype=unspecified

stringtype (String) Default null: Specify the type to use when binding PreparedStatement parameters set via setString() . If stringtype is set to VARCHAR (the default), such parameters will be sent to the server as varchar parameters. If stringtype is set to unspecified , parameters will be sent to the server as untyped values, and the server will attempt to infer an appropriate type. This is useful if you have an existing application that uses setString() to set parameters that are actually some other type, such as integers, and you are unable to change the application to use an appropriate method such as setInt() .

Slur

Answer 14

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

Progress

Answer 15

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>

Prohibit

Answer 16

Here is what you can do to fix it in PostgreSQL:

ALTER TABLE schema_name."table_name" 
 ALTER COLUMN "column_name" TYPE integer USING(same_column_name_again::INTEGER);

By doing this, you will change the column data type, fixing the issue of mismatch between column type to the Java entity data type.

Hugohugon