Java PostgreSQL 提示：您需要重写或转换表达式。列“状态”的类型是状态，但表达式的类型是字符变化
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45873514/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL Hint: You will need to rewrite or cast the expression. column "state" is of type status but expression is of type character varying
提问by Gatiivs
I am trying to create a SQL statement using java. The problem is I am using
我正在尝试使用 java 创建一个 SQL 语句。问题是我正在使用
stmt.setString(9, ev.getState().status());
for a variable I am trying to insert into a SQL column of type status
对于我试图插入到状态类型的 SQL 列中的变量
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?
我是犯了错误还是我真的需要在 sql 中转换值？如果是，在这种情况下如何施放？
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();
采纳答案by Pavel Stehule
You are using Prepared Statements - PostgreSQL get info from client side, so parameter is varcharbecause you are using setStringmethod. You should to inform Postgres, so input datatype is different with explicit cast.
您正在使用准备好的语句 - PostgreSQL 从客户端获取信息，因此参数是varchar因为您正在使用setString方法。您应该通知 Postgres，因此输入数据类型与显式转换不同。
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 varcharto date, enum, int, ...
所有数据都以文本形式传递（默认） - 因此传递的值没有问题。PostgreSQL 使用严格的类型系统 - 并且没有显式转换不允许从varcharto date, enum, int, ...
回答by MrNinjamannn
you must will be have same problem if you use DBeaver.
如果您使用 DBeaver，您一定会遇到同样的问题。
try:
尝试：
1.right click on your postgres db
1.右键单击您的postgres db
2.edit connection/general/database
2.编辑连接/通用/数据库
3.change database to the base in the context of which you want to make changes
3.将数据库更改为要进行更改的上下文中的基础
回答by abbas
This will be your fix
这将是你的修复
stmt.setObject(9, ev.getState().status(), Types.OTHER);
