insert timestamp value into postgresql

[勾王敏浩 <gw...@mails.tsinghua.edu.cn>]

Hi,
My Name is Wangminhao Gou,I hava used calcite to operate several relational databases including PG，however，when I want to insert timestamp value，there is an exception.The information is like this:
org.apache.calcite.runtime.CalciteContextException: From line 1, column 47 to line 1, column 60: Cannot assign to target field 'plt_createtime' of type TIMESTAMP(3) from source field 'EXPR$1' of type CHAR(23)
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Cannot assign to target field 'plt_createtime' of type TIMESTAMP(3) from source field 'EXPR$1' of type CHAR(23)
however when I used the same SQL statement in the navicat towards the PG,it succeeds.
What should I do then?Thank you.


Sincerely,
Wangminhao Gou

Re: insert timestamp value into postgresql

[Julian Hyde <jh...@apache.org>]

Calcite is certainly poor at doing implicit type casts (e.g. VARCHAR to TIMESTAMP).

However, it can do implicit conversions between types with the same type name for type family but different precision/scale. You can compare a DECIMAL(5, 0) with a DECIMAL(4, 2), for example. And you can insert a CHAR(3) into a VARCHAR(10) column. In other words, there is more flexibility as long as you are within the same type family.

We try hard to be consistent with the SQL standard, and I believe that we are. However we should definitely go further and allow more flexibility.

Julian
 

> On Mar 28, 2019, at 7:24 PM, Yuzhao Chen <yu...@gmail.com> wrote:
> 
> Calcite does not support implicit type cast yet, so it is strict for type compare, e.g. two decimals with different precision or scale will be considered different type. For timestamp(3) and varchar, they are definitely different data types.
> 
> You can make explicit type cast before inserting into target table.
> 
> Best,
> Danny Chan
> 在 2019年3月29日 +0800 AM9:44，勾王敏浩 <gw...@mails.tsinghua.edu.cn>，写道：
>> Hi,
>> My Name is Wangminhao Gou,I hava used calcite to operate several relational databases including PG，however，when I want to insert timestamp value，there is an exception.The information is like this:
>> org.apache.calcite.runtime.CalciteContextException: From line 1, column 47 to line 1, column 60: Cannot assign to target field 'plt_createtime' of type TIMESTAMP(3) from source field 'EXPR$1' of type CHAR(23)
>> Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Cannot assign to target field 'plt_createtime' of type TIMESTAMP(3) from source field 'EXPR$1' of type CHAR(23)
>> however when I used the same SQL statement in the navicat towards the PG,it succeeds.
>> What should I do then?Thank you.
>> 
>> 
>> Sincerely,
>> Wangminhao Gou
>> 

Re: insert timestamp value into postgresql

[Yuzhao Chen <yu...@gmail.com>]

Calcite does not support implicit type cast yet, so it is strict for type compare, e.g. two decimals with different precision or scale will be considered different type. For timestamp(3) and varchar, they are definitely different data types.

You can make explicit type cast before inserting into target table.

Best,
Danny Chan
在 2019年3月29日 +0800 AM9:44，勾王敏浩 <gw...@mails.tsinghua.edu.cn>，写道：
> Hi,
> My Name is Wangminhao Gou,I hava used calcite to operate several relational databases including PG，however，when I want to insert timestamp value，there is an exception.The information is like this:
> org.apache.calcite.runtime.CalciteContextException: From line 1, column 47 to line 1, column 60: Cannot assign to target field 'plt_createtime' of type TIMESTAMP(3) from source field 'EXPR$1' of type CHAR(23)
> Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Cannot assign to target field 'plt_createtime' of type TIMESTAMP(3) from source field 'EXPR$1' of type CHAR(23)
> however when I used the same SQL statement in the navicat towards the PG,it succeeds.
> What should I do then?Thank you.
>
>
> Sincerely,
> Wangminhao Gou
>