shell脚本调用psql执行\COPY导入数据报TO_DATE语法错误如何解决

1. 核心语法错误：PostgreSQL的COPY/\COPY命令的目标列列表仅支持填写目标表的列名，不支持直接在列名后直接追加转换函数、占位符表达式，你写的SCAN_DT \"TO_DATE(:SCAN_DT, 'mm/dd/yyyy')\"属于非法语法，直接触发语法报错。
2. 语法遗漏：RLSE_TM对应的TO_DATE函数缺少右括号，你写的TO_DATE(:RLSE_TM, 'HH24:MI:SS'少了收尾的)。
3. 格式不匹配：待导入文件中RLSE_TM字段的时间分隔符为.（示例值为11.06.31），你配置的格式字符串用了冒号分隔，即使语法修正也会导入失败。

方案1：使用TRANSFORM参数（PostgreSQL 14及以上版本适用） #

PostgreSQL 14开始支持COPY命令的TRANSFORM参数，可以直接在导入时对字段做类型转换，修正后的脚本如下：

infile="oos_item_dtl_stat.dat";

psql -tAqc "
\COPY DP.OOS_ITEM_DETAIL_STAT (
  SCAN_DT, SCAN_TM, SCAN_NBR, SCANNED_NBR_TYP_CD, UPC_ID,
  PROMO_IND_CD, EXPRESS_PROC_IND, SCAN_TYP_CD, USER_INITIAL_ID,
  RLSE_DTE, RLSE_TM
)
FROM '$infile' WITH (
  DELIMITER '|',
  NULL ' ',
  TRANSFORM (
    SCAN_DT WITH TO_DATE(?, 'mm/dd/yyyy'),
    SCAN_TM WITH TO_TIMESTAMP(?, 'HH24:MI:SS')::time,
    RLSE_DTE WITH TO_DATE(?, 'mm/dd/yyyy'),
    RLSE_TM WITH TO_TIMESTAMP(?, 'HH24.MI.SS')::time
  )
);
"

方案2：临时表中转方案（兼容所有PostgreSQL版本） #

如果你的PG版本低于14，可以用临时表中转的方式实现导入，脚本如下：

infile="oos_item_dtl_stat.dat";

psql -tAqc "
-- 创建临时表存储原始字符串数据
CREATE TEMP TABLE tmp_oos_item (
  scan_dt_str varchar,
  scan_tm_str varchar,
  scan_nbr varchar,
  scanned_nbr_typ_cd varchar,
  upc_id varchar,
  promo_ind_cd varchar,
  express_proc_ind varchar,
  scan_typ_cd varchar,
  user_initial_id varchar,
  rlse_dte_str varchar,
  rlse_tm_str varchar
);

-- 导入原始数据到临时表
\COPY tmp_oos_item FROM '$infile' WITH (DELIMITER '|', NULL ' ');

-- 转换类型后插入正式表，类型转换规则可根据正式表实际字段类型调整
INSERT INTO DP.OOS_ITEM_DETAIL_STAT (
  SCAN_DT, SCAN_TM, SCAN_NBR, SCANNED_NBR_TYP_CD, UPC_ID,
  PROMO_IND_CD, EXPRESS_PROC_IND, SCAN_TYP_CD, USER_INITIAL_ID,
  RLSE_DTE, RLSE_TM
)
SELECT
  TO_DATE(scan_dt_str, 'mm/dd/yyyy'),
  TO_TIMESTAMP(scan_tm_str, 'HH24:MI:SS')::time,
  scan_nbr,
  scanned_nbr_typ_cd,
  upc_id,
  promo_ind_cd,
  express_proc_ind,
  scan_typ_cd,
  user_initial_id,
  TO_DATE(rlse_dte_str, 'mm/dd/yyyy'),
  TO_TIMESTAMP(rlse_tm_str, 'HH24.MI.SS')::time
FROM tmp_oos_item;
"

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