PG插入内容报格式不对：text改int类型

1、需求

需求是想要在现有表上插入后续日期的数据，查询语句为：

insert into "工作表xx" ( SELECT to_date("inputtime"::text,'yyyy-mm-dd'),"字段1","字段2","字段3",COUNT("字段4") as num1 ,count(distinct("字段5")) as num2 from 来源表 WHERE "inputtime" BETWEEN '20x2-01-01' AND '20x2-12-31' GROUP BY to_date("inputtime"::text,'yyyy-mm-dd'),"字段1","字段2","字段3" )

之前有更新过数据，但这次跑的时候报了column "num1" is of type bigint but expression is of type text的错误

2、解决方案1 CAST转换

看了下目标字段类型为int8，于是将语句改了下：

insert into "工作表xx" ( SELECT to_date("inputtime"::text,'yyyy-mm-dd'),"字段1","字段2","字段3",CAST(COUNT("字段4") AS bigint) as num1 ,count(distinct("字段5")) as num2 from 来源表 WHERE "inputtime" BETWEEN '20x2-01-01' AND '20x2-12-31' GROUP BY to_date("inputtime"::text,'yyyy-mm-dd'),"字段1","字段2","字段3" )

在修改语句中，使用 CAST() 函数将 COUNT("字段4") 的结果转换为 bigint 类型，以匹配 "num1" 列的数据类型。

结果是不行，依然存在字段格式不对的问题

3、解决方案2 ::bigint转换

尝试将字段通过bigint来转换，通过使用 COUNT("字段4")::bigint 进行数据类型转换，以将COUNT函数的结果转换为bigint类型，以匹配"num1"列的数据类型：

insert into "工作表xx" ( SELECT to_date("inputtime"::text,'yyyy-mm-dd'),"字段1","字段2","字段3",COUNT("字段4")::bigint as num1 ,count(distinct("字段5")) as num2 from 来源表 WHERE "inputtime" BETWEEN '20x2-01-01' AND '20x2-12-31' GROUP BY to_date("inputtime"::text,'yyyy-mm-dd'),"字段1","字段2","字段3" )

 但结果依然是不行，查询结果报了同样的错误。

4、解决方案3 ::bigint转换

将COUNT("字段4")的结果进行bigint转换可能无法解决问题，又尝试使用SUM(1)来计算每行的总数，并使用CAST()函数将结果转换为bigint类型。

insert into "工作表xx" ( SELECT to_date("inputtime"::text,'yyyy-mm-dd'),"字段1","字段2","字段3",CAST(SUM(1) AS bigint) AS num1 ,count(distinct("字段5")) as num2 from 来源表 WHERE "inputtime" BETWEEN '20x2-01-01' AND '20x2-12-31' GROUP BY to_date("inputtime"::text,'yyyy-mm-dd'),"字段1","字段2","字段3" )

结果依然是不行

5、解决方案4 隐式类型

各种方法都不行，担心有隐私类型，所以尝试使用隐式类型转换，使用 COUNT("字段4")::text::bigint 将 COUNT("Log_Id") 的结果先转换为text类型，然后再转换为bigint类型.

insert into "工作表xx" ( SELECT to_date("inputtime"::text,'yyyy-mm-dd'),"字段1","字段2","字段3",COUNT("字段4")::text::bigint AS num1 ,count(distinct("字段5")) as num2 from 来源表 WHERE "inputtime" BETWEEN '20x2-01-01' AND '20x2-12-31' GROUP BY to_date("inputtime"::text,'yyyy-mm-dd'),"字段1","字段2","字段3" )

结果...依然是不行。

6、解决方案5 创建临时表

再次确认了下目标表的结构是int8，转换怎么都转不过去。最后打算试下临时表，将结果先存储在临时表中，然后再通过INSERT INTO语句将数据插入目标表。

最后得到了解决。