从JSON提取日期插入PostgreSQL表时遇类型转换错误求助

错误原因分析 #

1. 第一个错误：使用->>提取的日期是文本类型，但目标表的BeginningDate和EndDate列是timestamp without time zone类型，PostgreSQL不会自动进行类型转换，因此需要显式转换。
2. 第二个错误：错误地尝试将整个JSON对象直接转换为timestamp，这是非法操作——必须先提取JSON中的日期字符串字段，再进行类型转换。

正确的SQL语句 #

将提取出的日期字符串显式转换为timestamp without time zone类型即可，修改后的插入语句如下：

INSERT INTO Channel_Info(ID,ChannelID,SerialNumber,BeginningDate,EndDate)
select
       row_number() over (order by ChannelID::int ) as ID,
       ChannelID::int,
       c::json ->> 'SerialNumber' as SerialNumber,
       (c::json ->> 'BeginningDate')::timestamp without time zone as BeginningDate,
       (c::json ->> 'EndDate')::timestamp without time zone as EndDate
from tmp
CROSS JOIN LATERAL json_array_elements(c::json -> 'ChannelIDs') as ChannelID;

额外优化说明 #

• ChannelID的转换可以简化：json_array_elements返回的JSON数字可以直接转换为int类型，无需先转文本再转整数。
• 若日期格式是非标准ISO格式，可使用to_timestamp函数指定格式转换，例如：

  to_timestamp(c::json ->> 'BeginningDate', 'YYYY-MM-DD"T"HH24:MI:SS')::timestamp without time zone
  

  但你的日期是标准ISO格式，直接用::timestamp without time zone即可完成转换。

内容的提问来源于stack exchange，提问作者ChrisAsi71