phoenix upsert date 类型数据

最新推荐文章于 2022-03-21 15:22:24 发布

JerryDeng

最新推荐文章于 2022-03-21 15:22:24 发布

阅读量3.4k

点赞数

在phoenix中upsert date类型数据如下sql:

upsert into "test_date_type" (ID,"birthday") values (102, '2013-09-01 09:00:02');

执行时会有如下错误信息：

com.salesforce.phoenix.schema.TypeMismatchException: ERROR 203 (22005): Type mismatch. DATE and VARCHAR for 2013-09-01 09:00:02
    at com.salesforce.phoenix.expression.LiteralExpression.newConstant(LiteralExpression.java:126)
    at com.salesforce.phoenix.expression.LiteralExpression.newConstant(LiteralExpression.java:108)
    at com.salesforce.phoenix.compile.UpsertCompiler$UpsertValuesCompiler.visit(UpsertCompiler.java:633)
    at com.salesforce.phoenix.compile.UpsertCompiler$UpsertValuesCompiler.visit(UpsertCompiler.java:609)
    at com.salesforce.phoenix.parse.LiteralParseNode.accept(LiteralParseNode.java:70)
    at com.salesforce.phoenix.compile.UpsertCompiler.compile(UpsertCompiler.java:554)
    at com.salesforce.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.compilePlan(PhoenixStatement.java:290)
    at com.salesforce.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.optimizePlan(PhoenixStatement.java:295)
    at com.salesforce.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.executeUpdate(PhoenixStatement.java:279)
    at com.salesforce.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.execute(PhoenixStatement.java:272)
    at com.salesforce.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1014)>

通常在关系型数据库中我们可以用字符串来表示一个date类型，但是在phoenix中是不行的，会抛出TypeMismatchException: ERROR 203 (22005): Type mismatch. DATE and VARCHAR异常。

正确的做法是使用TO_DATE函数：

upsert into "test_date_type" (ID,"birthday") values (102, TO_DATE('2013-09-01 09:00:02'));