列的类型为timestamp，没有时区，但表达式的类型是可变字符:Nifi [英] Column is of type timestamp without time zone but expression is of type character varying : Nifi

问题描述

我正在尝试使用nifi将CSV记录插入Postgres数据库.

I am trying to insert CSV records to Postgres database using nifi.

csv文件示例:

timenow,location
'2019-10-21 15:13:11','colombo'

在nifi方案运行时，会出现以下错误

When nifi scenario runs, it gives following error

错误列'timenow'的类型为timestamp，没有时区，但表达式的类型是字符变化

Error column 'timenow' is of type timestamp without time zone but expression is of type character varying

如果我使用普通的insert into conditions (timenow,location) values ('2019-10-21 15:13:11','colombo');

没有错误值插入到表中.我是否需要更改nifi配置才能将其添加到表中?

No errors values inserted to table. Do I have to change nifi configurations to add this to table?

我更改了Nifi CSVReader->时间戳格式YYYY-MM-DD HH:MM:SS

I have changed Nifi CSVReader -> Timestamp format YYYY-MM-DD HH:MM:SS

在第一个评论之后 Nifi生成的查询是:INSERT INTO public.conditions (timenow, location) VALUES ('2019-10-21 15:13:11','colombo')

After first comment Nifi generated query is : INSERT INTO public.conditions (timenow, location) VALUES ('2019-10-21 15:13:11','colombo')

我在timescaledb终端上使用了它，并插入了记录.

I have used this with timescaledb terminal and it inserted the record.

推荐答案

这是nifi方面的简单修复.

It was a simple fix in nifi side.

在 DBCPConnectionPool 中->数据库URL-> jdbc:postgresql://localhost:5432/databaseName?stringtype=unspecified

In DBCPConnectionPool -> Database URL -> jdbc:postgresql://localhost:5432/databaseName?stringtype=unspecified

ajaytigga0210在日期和时间戳列面临的问题

Answer was given by ajaytigga0210 on Facing issues with Date and Timestamp Columns

这篇关于列的类型为timestamp，没有时区，但表达式的类型是可变字符:Nifi的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！