Skip site navigation (1) Skip section navigation (2)

PSQLException: ERROR: operator does not exist: integer = character varying

From: "Anderson, Mark S(dot)" <anderson(at)mitre(dot)org>
To: "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>
Subject: PSQLException: ERROR: operator does not exist: integer = character varying
Date: 2010-06-01 18:48:13
Message-ID: D93B26E07F2DD147A3E17BA1602444B80980792662@IMCMBX2.MITRE.ORG (view raw or flat)
Thread:
Lists: pgsql-jdbc
Hi Folks,

PSQLException: ERROR: operator does not exist: integer = character varying  [see full error messages below.]

This error occurs when I attempt to add a parameter to a prepared statement.  Everything I've read indicates that the likely cause is that I do not use the right datatype when adding the parameter to my prepared statement.  For example,

PreparedStatement ps = conn.prepareStatement("select article_title from articles where article_id = ?");
ps.setString(urlParameters.get("article_id"));

Here article_id is an integer in the database, but the parameter I set is a string.

However, I am not using Java directly, so I don't have a ps.setString() statement.  I am using the JSTL, specifically, the SQL tag library.

This is my code:
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>

<c:set var="query" value="
  select a.article_id, a.title, a.pub_year, a.article_type, a.citation, au.lname, au.fname
  from (articles a inner join authors_articles aa on a.article_id = aa.article_id)
  inner join authors au on aa.author_id = au.author_id
  where a.article_id = ?"
  />

<sql:query var="articleInfo" sql="${query}">
      <sql:param value="${param.article_id}" />
</sql:query>

The error occurs on the <sql:param ...> line.  "param.article_id" is indeed a string (it's a URL parameter).  The problem that I have is that I can't specify the datatype of ${param.article_id} or convert it to an integer.  I've tried to cast it to an int using to_number() in the query, but I get "function does not exist" from Postgres.

How can I convert the string to an int in my JSP file?

Mark

root cause

javax.servlet.ServletException: javax.servlet.jsp.JspException:
                select a.article_id, a.title, a.pub_year, a.article_type, a.citation, au.lname, au.fname
                from (article a inner join authors_articles aa on a.article_id = aa.article_id)
                inner join authors au on aa.author_id = au.author_id
                where a.article_id = ?: 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: 276
                org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:858)
                org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:791)
                org.apache.jsp.showReference_jsp._jspService(showReference_jsp.java:174)
                org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
                javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
                org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
                org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)
                org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
                javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

root cause

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: 276
                org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
                org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
                org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
                org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
                org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
                org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
                org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doEndTag(Unknown Source)
                org.apache.jsp.showReference_jsp._jspx_meth_sql_005fquery_005f0(showReference_jsp.java:241)
                org.apache.jsp.showReference_jsp._jspService(showReference_jsp.java:96)
                org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
                javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
                org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
                org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)
                org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
                javax.servlet.http.HttpServlet.service(HttpServlet.java:717)


pgsql-jdbc by date

Next:From: Jason TesserDate: 2010-06-01 19:02:23
Subject: Re: Found a Bug in latest Driver (I THINK) and pg 8.4
Previous:From: Kevin GrittnerDate: 2010-06-01 18:15:48
Subject: Re: Found a Bug in latest Driver (I THINK) and pg 8.4

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group