Thread: Fw: how to resolve org.postgresql.util.PSQLException: ERROR:operator does not exist: text = integer?

>>>>> "Karen" == Karen Goh <karenworld@yahoo.com> writes:

 Karen> This is a continuation of my last problem - not sure if I should
 Karen> write from there cos the first part was solved with the help
 Karen> from this group.

 Karen> I was trying to get the matching tutor_id with the parameter
 Karen> values which is in this case subject_names from a table which
 Karen> contains both the tutor_id and the subject_names.

 Karen> Here's the error message:

When you post these questions, you really need to include the ACTUAL SQL
TEXT of the query, not just the code snippet that constructs the query
(because that way we can't see for sure what query actually got
constructed). The best place to find that is in the postgresql server's
own logs.

I would guess that the problem is that you're passing an integer
parameter where you ought to be passing a string.

-- 
Andrew (irc:RhodiumToad)

            String sql1 = "select tutor_id, subject_name from tutor_subject where subject_name in ("

                    + builder.deleteCharAt(builder.length() - 1).toString() + ")";

            PreparedStatement ps2 = connection.prepareStatement(sql1);

            ps2.setInt(1, tutor_id);           

            ps2.setString(2, item);   

Begin forwarded message:

On Tuesday, July 16, 2019, 2:14 PM, Karen Goh <karenworld@yahoo.com> wrote:

Attn to all:

For sone kind of strange reason I do not know why the mail is not sent to all.

I have normally wrote n replied to the last person who wrote but yahoo mail didn’t let me write at there.

Hope everyone receives this mail this time round.

N please help me.

Tks!

Sent from Yahoo Mail for iPhone

Begin forwarded message:

On Tuesday, July 16, 2019, 1:01 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Mon, Jul 15, 2019 at 6:59 PM Karen Goh <karenworld@yahoo.com> wrote:

May I know why did you say I am comparing subject_name to an integer ?

I am trying to get both tutor_id and subject_name from the same table based on the no of ? in subjectName as per parameterValues.

Again, you need to send messages to the mailing, not me personally.

That said you might want to first figure out what:

ps2.setInt(1, tutor_id); 

is supposed to accomplish in your code.

David J.

Hi,

This is a continuation of my last problem - not sure if I should write from there cos the first part was solved with the help from this group.

I was trying to get the matching tutor_id with the parameter values which is in this case subject_names from a table which contains both the tutor_id and the subject_names.

Here's the error message:

org.postgresql.util.PSQLException: ERROR: operator does not exist: text = integer

  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

  Position: 69

    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)

    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)

    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)

    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)

    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)

    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)

    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:118)

    at Controller.searchController.doPost(searchController.java:108)

Here's the code snippet which I do not know where I had gone wrong:

            String sql1 = "select tutor_id, subject_name from tutor_subject where subject_name in ("

                    + builder.deleteCharAt(builder.length() - 1).toString() + ")";

            PreparedStatement ps2 = connection.prepareStatement(sql1);

HashMap<Integer, ArrayList<String>> tutorSubject = new HashMap<Integer, ArrayList<String>>();            

for(String item : subjs) {

            int tutor_id = tutor.getTutor_id();

            ps2.setInt(1, tutor_id);            

            if (item != null) {

                subjs.add(item);

            ps2.setString(2, item);     

            }                          

            ps2.executeQuery();

        while (rs.next()) {

                    tutor_id = rs.getInt("tutor_id");  

                    subjs.add(rs.getString("subject_name"));

                                            

                        tutorSubject.put(tutor_id, subjs);    

                        System.out.println(tutorSubject);

                        }

            

            System.out.println("tutor ID=" + rs.getInt("tutor_id") + ", subjectName=" + rs.getString("subject_name"));

Hope someone could point out my mistake.

Thanks.

Sent from Yahoo Mail for iPhone

Begin forwarded message:

On Tuesday, July 16, 2019, 2:14 PM, Karen Goh <karenworld@yahoo.com> wrote:

Attn to all:

For sone kind of strange reason I do not know why the mail is not sent to all.

I have normally wrote n replied to the last person who wrote but yahoo mail didn’t let me write at there.

Hope everyone receives this mail this time round.

N please help me.

Tks!

Sent from Yahoo Mail for iPhone

Begin forwarded message:

On Tuesday, July 16, 2019, 1:01 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Mon, Jul 15, 2019 at 6:59 PM Karen Goh <karenworld@yahoo.com> wrote:

May I know why did you say I am comparing subject_name to an integer ?

I am trying to get both tutor_id and subject_name from the same table based on the no of ? in subjectName as per parameterValues.

Again, you need to send messages to the mailing, not me personally.

That said you might want to first figure out what:

ps2.setInt(1, tutor_id); 

is supposed to accomplish in your code.

David J.

-- 

Widdersdorfer Str. 415, 50933 Köln; Tel. +49 / 221 / 9544 010
HRB Köln HRB 75439, Geschäftsführer: Dr. Dirk Goldner

On Tuesday, July 16, 2019, 2:25 PM, Martin Stöcker <martin.stoecker@stb-datenservice.de> wrote:

Hi,

please post your statement but not all this java stuff.
Nobody knows the type and content of your builder varaible.

Regards Martin

Am 16.07.2019 um 08:17 schrieb Karen Goh:

Hi,

This is a continuation of my last problem - not sure if I should write from there cos the first part was solved with the help from this group.

I was trying to get the matching tutor_id with the parameter values which is in this case subject_names from a table which contains both the tutor_id and the subject_names.

Here's the error message:

org.postgresql.util.PSQLException: ERROR: operator does not exist: text = integer

  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

  Position: 69

    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)

    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)

    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)

    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)

    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)

    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)

    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:118)

    at Controller.searchController.doPost(searchController.java:108)

Here's the code snippet which I do not know where I had gone wrong:

            String sql1 = "select tutor_id, subject_name from tutor_subject where subject_name in ("

                    + builder.deleteCharAt(builder.length() - 1).toString() + ")";

            PreparedStatement ps2 = connection.prepareStatement(sql1);

HashMap<Integer, ArrayList<String>> tutorSubject = new HashMap<Integer, ArrayList<String>>();            

for(String item : subjs) {

            int tutor_id = tutor.getTutor_id();

            ps2.setInt(1, tutor_id);            

            if (item != null) {

                subjs.add(item);

            ps2.setString(2, item);     

            }                          

            ps2.executeQuery();

        while (rs.next()) {

                    tutor_id = rs.getInt("tutor_id");  

                    subjs.add(rs.getString("subject_name"));

                                            

                        tutorSubject.put(tutor_id, subjs);    

                        System.out.println(tutorSubject);

                        }

            

            System.out.println("tutor ID=" + rs.getInt("tutor_id") + ", subjectName=" + rs.getString("subject_name"));

Hope someone could point out my mistake.

Thanks.

Sent from Yahoo Mail for iPhone

Begin forwarded message:

On Tuesday, July 16, 2019, 2:14 PM, Karen Goh <karenworld@yahoo.com> wrote:

Attn to all:

For sone kind of strange reason I do not know why the mail is not sent to all.

I have normally wrote n replied to the last person who wrote but yahoo mail didn’t let me write at there.

Hope everyone receives this mail this time round.

N please help me.

Tks!

Sent from Yahoo Mail for iPhone

Begin forwarded message:

On Tuesday, July 16, 2019, 1:01 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Mon, Jul 15, 2019 at 6:59 PM Karen Goh <karenworld@yahoo.com> wrote:

May I know why did you say I am comparing subject_name to an integer ?

I am trying to get both tutor_id and subject_name from the same table based on the no of ? in subjectName as per parameterValues.

Again, you need to send messages to the mailing, not me personally.

That said you might want to first figure out what:

ps2.setInt(1, tutor_id); 

is supposed to accomplish in your code.

David J.

-- 

Widdersdorfer Str. 415, 50933 Köln; Tel. +49 / 221 / 9544 010
HRB Köln HRB 75439, Geschäftsführer: Dr. Dirk Goldner

>>>>> "Karen" == Karen Goh <karenworld@yahoo.com> writes:

 Karen> Attn to all:
 Karen> For sone kind of strange reason I do not know why the mail is
 Karen> not sent to all.

Does yahoo not provide a "reply to all" option? (If you just do a normal
reply it won't go to the list.)

-- 
Andrew (irc:RhodiumToad)

On Tuesday, July 16, 2019, 4:41 PM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:

>>>>> "Karen" == Karen Goh <karenworld@yahoo.com> writes:

Karen> Attn to all:
Karen> For sone kind of strange reason I do not know why the mail is
Karen> not sent to all.

Does yahoo not provide a "reply to all" option? (If you just do a normal
reply it won't go to the list.)

Believe me. It is not the first time I sent to mailing list. I sent to Apache Tomcat many times. Anyway, please direct me to the right mailing list rather than question me. Tks

--
Andrew (irc:RhodiumToad)

>>>>> "Karen" == Karen Goh <karenworld@yahoo.com> writes:

 Karen> Can I know which forum to post to cos I think this may be the
 Karen> wrong forum.

This is good enough (though the -general list would probably be better).

But you still need to show us the actual SQL text that gets sent to the
server, NOT just the java code that generates that SQL. You can get the
statement from the server logs (by default, the statement text is logged
alongside any error).

Please at least make some effort to do that before asking again.

-- 
Andrew (irc:RhodiumToad)

Karen Goh schrieb am 16.07.2019 um 10:21:
> Can I know which forum to post to cos I think this may be the wrong
> forum. Cos it is sql so this forum maybe for sql execute inside the
> database. However, I read there is no more jdbc forum, correct me if
> I am wrong.

While this mailing lists indeed deals with plain SQL topics, your problem is not completely off-topic. 

But the reason why you don't get help is, that you are not supplying enough information so that we can help you.

The code you have shown us, doesn't show the *actual* SQL String that is generated (and send to the database). 

So it's impossible to know to which parameter the error message refers to 
Is it the use of "ps2.setInt(1, tutor_id);"  or maybe the use of "ps2.setString(2, item)"?

It's vital to know how that parameter is used in the actual (generated) SQL query. 

Given the error message, I assume(!) that the tutor_id is used together with a varchar/text column. 

However Postgres will refuse to compare integer and string values. 
That would be similar to writing "someStringVariable.equals(42)" in Java - you wouldn't do that either. 

So what we need from you in order to be able to help you is: 

* The actual generated and complete SQL string of the query you are building. 
  Add System.out.println(sql1) to your code and then show us that result
* The definition of the table columns that are used together with the parameters (the "?" in the printed SQL string)




Thomas

Sent with Mozilla Thunderbird

On Tuesday, July 16, 2019, 4:54 PM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:

>>>>> "Karen" == Karen Goh <karenworld@yahoo.com> writes:

Karen> Can I know which forum to post to cos I think this may be the
Karen> wrong forum.

This is good enough (though the -general list would probably be better).

But you still need to show us the actual SQL text that gets sent to the
server, NOT just the java code that generates that SQL. You can get the
statement from the server logs (by default, the statement text is logged
alongside any error).

Ok. Thanks. Just to confirm, I need log4j2 or SL4J to generate the log right? 

Please at least make some effort to do that before asking again.

--
Andrew (irc:RhodiumToad)

>>>>> "Karen" == Karen Goh <karenworld@yahoo.com> writes:

 >> You can get the statement from the server logs (by default, the
 >> statement text is logged alongside any error).
 
 Karen> Ok. Thanks. Just to confirm, I need log4j2 or SL4J to generate
 Karen> the log right?

No.

PostgreSQL writes its own logs. Most installs have some degree of
logging enabled by default, though this does depend on what package was
installed and how the server was started. In some cases you can figure
out where the log files are from the following server settings (use the
SHOW statement to display them):

1. If log_destination is "syslog", then whether a log file exists and
   where it is depends on the system's syslog.conf file.

2. If log_destination is "eventlog", then this is a Windows system, and
   log messages are in the Windows event log.

3. If log_destination is neither of the above, and logging_collector is
   enabled, then the log_directory setting shows where the log files are
   (if this is a relative path, it's relative to data_directory).

4. If log_destination is "stderr" and logging_collector is _disabled_,
   then the logs (if any) are written to wherever the startup script
   directed them. This depends on the OS and packaging.

-- 
Andrew (irc:RhodiumToad)

On Tuesday, July 16, 2019, 5:36 PM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:

>>>>> "Karen" == Karen Goh <karenworld@yahoo.com> writes:

>> You can get the statement from the server logs (by default, the
>> statement text is logged alongside any error).

Karen> Ok. Thanks. Just to confirm, I need log4j2 or SL4J to generate
Karen> the log right?

No.

PostgreSQL writes its own logs. Most installs have some degree of
logging enabled by default, though this does depend on what package was
installed and how the server was started. In some cases you can figure
out where the log files are from the following server settings (use the
SHOW statement to display them):

1. If log_destination is "syslog", then whether a log file exists and
  where it is depends on the system's syslog.conf file.

2. If log_destination is "eventlog", then this is a Windows system, and
  log messages are in the Windows event log.

3. If log_destination is neither of the above, and logging_collector is
  enabled, then the log_directory setting shows where the log files are
  (if this is a relative path, it's relative to data_directory).

4. If log_destination is "stderr" and logging_collector is _disabled_,
  then the logs (if any) are written to wherever the startup script
  directed them. This depends on the OS and packaging.

How do you guys use PostgreSQL ? I do not know  other ways except PGADMIN4. Is there a tutorial I can read up so that my database that I have created can be access via PostgreSQL? 

Last time when my MYSQL was still working, I can just write MySQL d something like that but PostGreSQL I examined the bin there is not command line tool whatsoever....:(

--
Andrew (irc:RhodiumToad)

>>>>> "Karen" == Karen Goh <karenworld@yahoo.com> writes:

 Karen> How do you guys use PostgreSQL ? I do not know other ways except
 Karen> PGADMIN4.

This isn't relevant to your question - the process of finding the server
log files would be exactly the same even if you were using the "psql"
command-line tool.

 Karen> Is there a tutorial I can read up so that my database that I
 Karen> have created can be access via PostgreSQL?

This question does not make sense - if you created a database then it is
accessible.

 Karen> Last time when my MYSQL was still working, I can just write
 Karen> MySQL d something like that but PostGreSQL I examined the bin
 Karen> there is not command line tool whatsoever....:(

Every commonly-used packaging of PostgreSQL includes all the
command-line tools. (Remember that pgadmin4 is a _separate project_ from
PostgreSQL itself.)

As far as I can tell, in all of this exchange you have not told us what
operating system(s) you are using, whether you're running the database
server locally or remotely, what version of what packages you installed
(and where you obtained them). How can we give you advice without
knowing any of these details?

-- 
Andrew (irc:RhodiumToad)

>>>>> "Karen" == Karen Goh <karenworld@yahoo.com> writes:

 Karen> Do you mind tell me where do I download PostgreSQL? I am using
 Karen> Windows 10 as OS.

https://www.postgresql.org/download/windows/

This links to the EnterpriseDB installer package, which is the one you
want (so it'll take you to the EnterpriseDB site).

(But I have to ask, is this part of some actual plan to solve your
original problem? You clearly already have a postgresql server that you
are connecting to, otherwise you wouldn't get that error.)

-- 
Andrew (irc:RhodiumToad)

>>>>> "Karen" == Karen Goh <karenworld@yahoo.com> writes:

 Karen> Yes. I have downloaded and it is inside my Program Files.

What exactly is in your Program Files?

 Karen> And now I am getting another error :

 Karen> org.postgresql.util.PSQLException: ERROR: operator does not exist: text = character varying[]

Someone just asked this a few minutes ago on the IRC channel but left
without acknowledging my response; was that you?

 Karen> But, I check stackoverflow, it is mentioned that text is the
 Karen> best definition. And this is what I used for subject_name which
 Karen> is a list I am getting from the In clause.

character varying[] means _array of varchar_, of course you can't
compare an array directly against a scalar.

If you want to pass an array value as a parameter, you can't use IN (x), you
have to use  = ANY (x)  instead.

-- 
Andrew (irc:RhodiumToad)

>>>>> "Karen" == Karen Goh <karenworld@yahoo.com> writes:

 >> What exactly is in your Program Files?

 Karen> PostgreSQL which has folder named 10 and then open up you will
 Karen> get to see bin folder, data folder, script folder ....

OK, and did you try doing "SHOW log_destination;" as a query, for
example in the pgadmin4 query window? also "SHOW logging_collector;"
and "SHOW data_directory;"

 >> If you want to pass an array value as a parameter, you can't use IN
 >> (x), you have to use = ANY (x) instead.

 Karen> Is Any (x) this format applies only to Postgresql ?

scalar = ANY (arrayvalue)  is a postgresql extension, yes.

The SQL spec says that the syntax

(col IN (1,2,3))

is equivalent to

(col = ANY (VALUES (1),(2),(3)))

but this doesn't allow you to pass a single array parameter for the IN
list either in PostgreSQL or in standard SQL. In both PostgreSQL and
standard SQL you can do:

(col = ANY (select v from unnest(arrayvalue) as u(v)))

but this isn't the recommended style for PostgreSQL because it is less
efficient.

-- 
Andrew (irc:RhodiumToad)

>>>>> "Karen" == Karen Goh <karenworld@yahoo.com> writes:

 Karen> I have been told In clause in the way to do it.
 Karen> So, not sure why am I getting that error....

Because the IN clause requires a list (an explicitly written out list,
not an array) of values of the same type (or at least a comparable type)
of the predicand.

i.e. if "col" is a text column, these are legal syntax:

col IN ('foo', 'bar', 'baz')   -- explicit literals

col IN (?, ?, ?)   -- some fixed number of placeholder parameters

(in that second case, the parameters should be of type text or varchar)

but these are not legal and will give a type mismatch error:

col IN (array['foo','bar'])   -- trying to compare text and text[]

col IN (?)  -- where the parameter type is given as text[] or varchar[]

There is no way in either standard SQL or PostgreSQL to use IN to
specify a variable-length parameter array of values to compare against.

Some people (including, alas, some authors of database drivers, looking
at you psycopg2) try and work around this by dynamically interpolating
values or parameter specifications into the query. This is BAD PRACTICE
and you should never do it; keep your parameter values AWAY from your
query strings, for security.

-- 
Andrew (irc:RhodiumToad)