Bash脚本导入CSV至PostgreSQL报错：多余逗号引发语法错误

错误信息 #

ERROR: syntax error at or near "," LINE 1: ...1", "2023-12-10 04:59:41", "2023-12-10 05:00:48", , "-", , "... ^

对应CSV行数据 #

1,"+18654752227","+18653509420","+12009990871","2023-12-10 04:59:41","2023-12-10 04:59:41","2023-12-10 05:00:48","-","-","16 No error","?","INT","Local","no indication","true",""

现有Bash脚本 #

#!/bin/bash
export DBHost="######"
export DBPort="####"
export DBUser="#######"
export DBName="######"
export PGPASSWORD='#######'
export DBTable="######"
export File=$1 

while read -r line
do
        IFS=',' read -ra values <<< "$line"
        Seq_NO="${values[0]}"
        Charge_Number="${values[1]}"
        Calling_Number="${values[2]}"
        Called_Number="${values[3]}"
        Seize_Time="${values[4]}"
        Answer_Time="${values[5]}"
        Release_Time="${values[6]}"
        Inbound_Trunk="${vales[7]}"
        Outbound_Trunk="${values[8]}"
        Release="${values[9]}"
        Redirect="${values[10]}"
        Topology="${values[11]}"
        Type="${values[12]}"
        Privacy="${values[13]}"
        Answered="${values[14]}"
        Carrier="${values[15]}"

        psql -h "$DBHost" -p "$DBPort" -U "$DBUser" -d "$DBName" -c "INSERT INTO CDR (Seq_NO, Charge_Number, Calling_Number, Called_Number, Seize_Time, Answer_Time, Release_Time, Inbound_Trunk, Outbound_Trunk, Rlease, Redirect, Topology, Type, Privacy, Answered, Carrier) VALUES ($Seq_NO, $Charge_Number, $Calling_Number, $Called_Number, $Seize_Time, $Answer_Time, $Release_Time, $Inbound_Trunk, $Outbound_Trunk, $Rlease, $Redirect, $Topology, $Type, $Privacy, $Answered, $Carrier)"
done < $1

问题原因及解决方案 #

1. 拼写错误导致变量为空 #

脚本中Inbound_Trunk="${vales[7]}"是拼写错误，应为values[7]，错误会导致$Inbound_Trunk为空，插入SQL时出现连续逗号。同时INSERT语句里的字段Rlease应为Release，对应变量$Release，错误会导致$Rlease为空，也是连续逗号的来源。

2. CSV解析缺陷 #

直接用IFS=','拆分带引号的CSV字段，会导致空字段（如最后一个""）处理异常，且无法应对字段内包含逗号的情况。

3. SQL语法与注入风险 #

直接拼接变量到SQL语句，会因字符串特殊字符（如引号）引发语法错误，同时存在SQL注入风险。

修复方案（推荐用高效的\COPY命令）

#!/bin/bash
export DBHost="######"
export DBPort="####"
export DBUser="#######"
export DBName="######"
export PGPASSWORD='#######'
export File="$1" 

# 使用PostgreSQL自带的\COPY命令，自动处理CSV格式、空值、带引号字段
psql -h "$DBHost" -p "$DBPort" -U "$DBUser" -d "$DBName" -c "\COPY CDR(Seq_NO, Charge_Number, Calling_Number, Called_Number, Seize_Time, Answer_Time, Release_Time, Inbound_Trunk, Outbound_Trunk, Release, Redirect, Topology, Type, Privacy, Answered, Carrier) FROM '$File' WITH (FORMAT csv, HEADER false, DELIMITER ',', QUOTE '\"', NULL '');"

若需循环插入的修复版本

#!/bin/bash
export DBHost="######"
export DBPort="####"
export DBUser="#######"
export DBName="######"
export PGPASSWORD='#######'
export File="$1" 

# 需先安装csvquote工具：sudo apt install csvquote 或 brew install csvquote
while IFS= read -r line
do
    # 处理带引号的CSV字段
    parsed_line=$(echo "$line" | csvquote)
    IFS=',' read -ra values <<< "$parsed_line"

    # 空字段转为NULL，字符串转义单引号（避免SQL语法错误）
    Seq_NO=${values[0]:-NULL}
    Charge_Number=${values[1]:+"'$(echo "${values[1]}" | csvquote -u | sed "s/'/''/g")'"}:-NULL
    Calling_Number=${values[2]:+"'$(echo "${values[2]}" | csvquote -u | sed "s/'/''/g")'"}:-NULL
    Called_Number=${values[3]:+"'$(echo "${values[3]}" | csvquote -u | sed "s/'/''/g")'"}:-NULL
    Seize_Time=${values[4]:+"'$(echo "${values[4]}" | csvquote -u | sed "s/'/''/g")'"}:-NULL
    Answer_Time=${values[5]:+"'$(echo "${values[5]}" | csvquote -u | sed "s/'/''/g")'"}:-NULL
    Release_Time=${values[6]:+"'$(echo "${values[6]}" | csvquote -u | sed "s/'/''/g")'"}:-NULL
    Inbound_Trunk=${values[7]:+"'$(echo "${values[7]}" | csvquote -u | sed "s/'/''/g")'"}:-NULL
    Outbound_Trunk=${values[8]:+"'$(echo "${values[8]}" | csvquote -u | sed "s/'/''/g")'"}:-NULL
    Release=${values[9]:+"'$(echo "${values[9]}" | csvquote -u | sed "s/'/''/g")'"}:-NULL
    Redirect=${values[10]:+"'$(echo "${values[10]}" | csvquote -u | sed "s/'/''/g")'"}:-NULL
    Topology=${values[11]:+"'$(echo "${values[11]}" | csvquote -u | sed "s/'/''/g")'"}:-NULL
    Type=${values[12]:+"'$(echo "${values[12]}" | csvquote -u | sed "s/'/''/g")'"}:-NULL
    Privacy=${values[13]:+"'$(echo "${values[13]}" | csvquote -u | sed "s/'/''/g")'"}:-NULL
    Answered=${values[14]:+"'$(echo "${values[14]}" | csvquote -u | sed "s/'/''/g")'"}:-NULL
    Carrier=${values[15]:+"'$(echo "${values[15]}" | csvquote -u | sed "s/'/''/g")'"}:-NULL

    psql -h "$DBHost" -p "$DBPort" -U "$DBUser" -d "$DBName" -c "INSERT INTO CDR (Seq_NO, Charge_Number, Calling_Number, Called_Number, Seize_Time, Answer_Time, Release_Time, Inbound_Trunk, Outbound_Trunk, Release, Redirect, Topology, Type, Privacy, Answered, Carrier) VALUES ($Seq_NO, $Charge_Number, $Calling_Number, $Called_Number, $Seize_Time, $Answer_Time, $Release_Time, $Inbound_Trunk, $Outbound_Trunk, $Release, $Redirect, $Topology, $Type, $Privacy, $Answered, $Carrier);"
done < "$File"

关键说明 #

• \COPY是PostgreSQL原生命令，处理CSV效率远高于循环插入，能自动处理带引号字段、空值等场景，避免手动解析的各类错误。
• 循环插入需依赖csvquote工具解析带引号的CSV字段，同时对字符串中的单引号进行转义，防止SQL语法错误。

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