Oracle SQL*Loader加载脚本转PostgreSQL Shell实现求助

导入报错的核心原因有两个：

1. PostgreSQL默认日期格式为YYYY-MM-DD，和源文件的MM/DD/YYYY格式不匹配
2. 源文件中RLSE_TM字段使用.作为时间分隔符，不符合默认的HH:MI:SS时间格式规范

推荐使用临时表中转方案，兼容性最强，无需修改源文件或数据库全局配置，完全适配原SQL*Loader的所有逻辑，对应Shell调用脚本如下：

psql -U 你的数据库用户名 -d 你的数据库名 << EOF
-- 1. 创建临时中转表，会话结束自动销毁无需手动清理
CREATE TEMP TABLE tmp_detail_stat (
    scan_dt_text TEXT,
    scan_tm_text TEXT,
    scan_nbr TEXT,
    scanned_nbr_typ_cd TEXT,
    upc_id TEXT,
    promo_ind_cd TEXT,
    express_proc_ind TEXT,
    scan_typ_cd TEXT,
    user_initial_id TEXT,
    rlse_dte_text TEXT,
    rlse_tm_text TEXT
) ON COMMIT DROP;

-- 2. 导入原始数据到临时表，无格式转换避免导入时报错
COPY tmp_detail_stat
FROM '/你的文件路径/stat.dat'
WITH (
    FORMAT csv,
    DELIMITER '|',
    NULL ' ',
    ENCODING 'UTF8',
    ON_ERROR 'continue'
);

-- 3. 格式转换后插入正式表，完全匹配原SQL*Loader的转换规则
INSERT INTO dt.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_text, 'MM/DD/YYYY'),
    TO_TIMESTAMP(scan_tm_text, 'HH24:MI:SS')::TIME,
    TRIM(scan_nbr),
    TRIM(scanned_nbr_typ_cd),
    TRIM(upc_id),
    TRIM(promo_ind_cd),
    TRIM(express_proc_ind),
    TRIM(scan_typ_cd),
    TRIM(user_initial_id),
    TO_DATE(rlse_dte_text, 'MM/DD/YYYY'),
    TO_TIMESTAMP(rlse_tm_text, 'HH24.MI.SS')::TIME
FROM tmp_detail_stat;
EOF

注意事项 #

• 如果stat.dat文件存放在客户端本地而非PostgreSQL服务器上，将COPY替换为\copy即可，无需超级用户权限
• 脚本中的数据库用户名、数据库名、文件路径需替换为你实际环境的对应值
• 若使用PostgreSQL 12以下版本不支持ON_ERROR 'continue'参数，可删除该参数，导入前先排查源文件中的脏数据
• ON_ERROR 'continue'参数对应原SQL*Loader的errors=999999999配置，会跳过错误行继续导入，错误信息会直接输出到控制台可自行排查
• 源数据中所有字段末尾带的空格会通过TRIM()函数自动去除，和原SQL*Loader的默认处理逻辑一致

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