PostgreSQL函数插入数据时类型转换失败问题求助

你的问题出在两个核心点：运算符优先级错误和日期格式不兼容，导致timestamp、numeric类型转换失败。

错误原因拆解 #

1. 运算符优先级问题：PostgreSQL中::类型转换的优先级高于->>（JSONB文本值提取），你原来的写法j.spec->>'crawldate'::timestamp，实际是把字符串'crawldate'尝试转成timestamp，而非提取到的"14-07-2022"，这必然触发转换错误。
2. 日期格式不匹配：你传入的日期是DD-MM-YYYY格式（14-07-2022），但PostgreSQL默认的timestamp解析规则不识别该格式，必须显式指定格式才能正确转换。

修正后的函数写法 #

以下是两种可行的修正方案：

方案1：基础修正写法 #

给JSONB值提取操作加括号，确保先取值再转换类型，同时用to_timestamp指定日期格式：

CREATE OR REPLACE FUNCTION api.test_newtable(spec jsonb)
 RETURNS void
LANGUAGE plpgsql
AS $BODY$
begin
  INSERT INTO api.newtable (check_date, filename, mysum)
  select 
    to_timestamp(j.spec->>'crawldate', 'DD-MM-YYYY')::timestamp,
    j.spec->>'filename',
    (j.spec->>'mysum')::numeric
  from jsonb_array_elements(spec) as j(spec);
end;
$BODY$;

方案2：用jsonb_to_record优雅映射类型（推荐） #

直接定义JSON结构与目标类型的映射，可读性更强，彻底规避优先级问题：

CREATE OR REPLACE FUNCTION api.test_newtable(spec jsonb)
 RETURNS void
LANGUAGE plpgsql
AS $BODY$
begin
  INSERT INTO api.newtable (check_date, filename, mysum)
  select 
    to_timestamp(r.crawldate, 'DD-MM-YYYY')::timestamp,
    r.filename,
    r.mysum::numeric
  from jsonb_array_elements(spec) as j(spec)
  cross join jsonb_to_record(j.spec) as r(
    crawldate text,
    filename text,
    mysum text
  );
end;
$BODY$;

验证调用 #

你原有的调用语句可直接使用，两种方案都能正确插入数据：

SELECT api.test_newtable('[
                              {
                                "crawldate":"14-07-2022",
                               "filename":"tesjjjt",
                                "mysum": "1"
                            }
                            ]'::jsonb);

如果你的mysum在JSON中是数字类型（如"mysum": 1而非"mysum": "1"），可将转换逻辑改为(j.spec->'mysum')::numeric，无需先转文本再转numeric，效率更高。

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