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

user_451135 0
Viewed 271242

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
eclipselink jpa postgresql orm java
13 Answers

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.

user_45399060

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.

user_110422390

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.

user_22721580

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
user_27888960

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.

user_27032390

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

user_75467880

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.

user_170613830
Related