Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL Hint: You will need to rewrite or cast the expression. column "state" is of type status but expression is of type character varying

I am trying to create a SQL statement using java. The problem is I am using

 stmt.setString(9, ev.getState().status());

for a variable I am trying to insert into a SQL column of type status

 CREATE TYPE STATUS AS ENUM ('APPROVED', 'CLOSED','STARTED', 'WAITING');

It is throwing me an exception of

column "state" is of type status but expression is of type character varying
Hint: You will need to rewrite or cast the expression.

Did I make a mistake or do I actually need to cast the value in sql? If yes, how does one cast in this situation?

Full Statement:

     PreparedStatement stmt = conn.prepareStatement("INSERT INTO Event (EventNum, EventName, startHour, endHour, startMin, endMin, startDate, endDate, State, depName) VALUES (?, ?, ?, ?, ?, ?, ?::date, ?::date, ?, ?)");




     stmt.setInt(1, ev.getEventNum());
     stmt.setString(2, ev.getName());
     stmt.setInt(3, ev.getStartHour());
     stmt.setInt(4, ev.getEndHour());
     stmt.setInt(5, ev.getStartMinute());
     stmt.setInt(6, ev.getEndMinute());
     stmt.setString(7, ev.getStartYear() + "-" + ev.getStartMonth() + "-" + ev.getStartDate());
     stmt.setString(8, ev.getEndYear() + "-" + ev.getEndMonth() + "-" + ev.getEndDate());
     stmt.setString(9, ev.getState().status());
     stmt.setString(10, ev.getDepartment());



     stmt.executeUpdate();
like image 322
Gatiivs Avatar asked Aug 25 '17 02:08

Gatiivs


3 Answers

You are using Prepared Statements - PostgreSQL get info from client side, so parameter is varchar because you are using setString method. You should to inform Postgres, so input datatype is different with explicit cast.

PreparedStatement stmt = conn.prepareStatement(
  "INSERT INTO Event (EventNum, EventName, startHour, endHour, startMin, endMin, startDate, endDate, State, depName)
               VALUES (?, ?, ?, ?, ?, ?, ?::date, ?::date, ?::status, ?)");

All data are passed in text form (it is default) - so there are not a problem with passed values. PostgreSQL uses strict type system - and without explicit casting don't allow cast from varchar to date, enum, int, ...

like image 89
Pavel Stehule Avatar answered Nov 16 '22 10:11

Pavel Stehule


you must will be have same problem if you use DBeaver.

try:

1.right click on your postgres db

2.edit connection/general/database

3.change database to the base in the context of which you want to make changes

like image 38
MrNinjamannn Avatar answered Nov 16 '22 10:11

MrNinjamannn


You cannot store a String in a Enum type field. You need to use the setObject method and also provide the type like this

stmt.setObject(9, ev.getState().status(), Types.OTHER);
like image 1
abbas Avatar answered Nov 16 '22 10:11

abbas