JDBC ERROR: operator does not exist: date = integer

12,405

I'm guessing your dates are probably being substituted in without quoting, like 2012-01-01 instead of '2012-01-01'. 2012-01-01 is an integer mathematical expression that results in the number 2010, so you're comparing a date to an integer. You need to quote your dates, or better, use proper prepared statements.

Why used prepared statements?

To demonstrate what I think your code's problem is, I think you're doing this:

regress=> SELECT DATE '2012-03-12' = 2012-03-12;
ERROR:  operator does not exist: date = integer
LINE 1: SELECT DATE '2012-03-12' = 2012-03-12;
                                 ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Observe:

regress=> \x
Expanded display is on.
regress=> SELECT 
              2012-03-12 AS unquoted,
              pg_typeof(2012-03-12) AS unquotedtype,
              '2012-03-12' AS quoted,
              pg_typeof('2012-03-12') AS quotedtype, 
              DATE '2012-03-12' AS typespecified,
              pg_typeof(DATE '2012-03-12') AS typespecifiedtype;
-[ RECORD 1 ]-----+-----------
unquoted          | 1997
unquotedtype      | integer
quoted            | 2012-03-12
quotedtype        | unknown
typespecified     | 2012-03-12
typespecifiedtype | date

(1 row)

If you won't use prepared statements, replace %s with DATE '%s', but please use prepared statements.

Can you add a statement to print the contents of checkAvailable_flight after formatting, then paste its output here to confirm or refute my guess?

Share:
12,405

Related videos on Youtube

johnnily
Author by

johnnily

Updated on September 15, 2022

Comments

  • johnnily
    johnnily about 1 year
        String checkAvailable_flight = String.format("SELECT Flightid, flightdate,"
                + " origin, destination FROM flight"
                + " WHERE  Flightdate::Date = %s  AND origin = %s"
                + " AND destination = %s;", date_, origin_, destination_);
    
        ResultSet rs = stmt.executeQuery(checkAvailable_flight);
    
        if (!rs.next()) {
    
            System.out.println("no data inserted");
        } else {
    
            do {
                int flightid = rs.getInt("flightid");
                String date = rs.getString("flightdate");
                String origin = rs.getString("origin");
                String destination = rs.getString("destination");
    
                System.out.printf("%-10d %5s %5s %7s\n",flightid, date, origin, destination);
    
            } while (rs.next());
        }
    

    Error(s) occurred:

    SQLException : ERROR: operator does not exist: date = integer
      Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
      Position: 86
    SQLState : 42883
    SQLCode : 0
    

    hello, i working on JDBC and wanna execute the sql query and print out the table ..but i got the error above..

    i try to casting the flightdate in another way, like:

    CAST(Flightdate AS TEXT) LIKE '2013-04-12%' 
    

    but the error still occurred....

    any suggestion will appreciate it..