Bash脚本插入PostgreSQL时winner_id与opponent_id语法错误求助

需要编写Bash脚本将世界杯比赛数据从games.csv导入PostgreSQL数据库，执行脚本时出现SQL语法错误，错误显示winner_id和opponent_id位置为空。

数据示例（games.csv前几行） #

year,round,winner,opponent,winner_goals,opponent_goals
2018,Final,France,Croatia,4,2
2018,Third Place,Belgium,England,2,0
2018,Semi-Final,Croatia,England,2,1
2018,Semi-Final,France,Belgium,1,0
2018,Quarter-Final,Croatia,Russia,3,2
2018,Quarter-Final,England,Sweden,2,0

原脚本（insert_data.sh） #

if [[ $1 == "test" ]]
then
  PSQL="psql --username=postgres --dbname=worldcuptest -t --no-align -c"
else
  PSQL="psql --username=freecodecamp --dbname=worldcup -t --no-align -c"
fi

# Do not change code above this line. Use the PSQL variable above to query your database.

echo $($PSQL "TRUNCATE TABLE games, teams")

cat games.csv | while IFS="," read YEAR ROUND WINNER OPPONENT WG OG
do

  if [[ $WINNER != "winner" ]]
  then 
  WINNER_ID=$($PSQL "SELECT team_id FROM teams WHERE name LIKE '$WINNER'")
      
    if [[ -z $WINNER_ID ]]
    then
    RESULT_INSERT_WINNER=$($PSQL "INSERT INTO teams (name) VALUES ('$WINNER')")
    fi

  fi        

  if [[ $OPPONENT != "opponent" ]]
  then 
  OPPONENT_ID=$($PSQL "SELECT team_id FROM teams WHERE name LIKE '$OPPONENT'")
      
    if [[ -z $OPPONENT_ID ]]
    then
    RESULT_INSERT_OPPONENT=$($PSQL "INSERT INTO teams (name) VALUES ('$OPPONENT')")
    fi
    
  fi

  if [[ $YEAR != "year" ]]
  then
    ADD_DATA=$($PSQL "INSERT INTO games(year, round, winner_id, opponent_id, winner_goals, opponent_goals) VALUES($YEAR, '$ROUND', $WINNER_ID, $OPPONENT_ID, $WG, $OG)")
  fi
done  

错误信息 #

ERROR:  syntax error at or near ","
LINE 1: ...inner_goals, opponent_goals) VALUES(2018, 'Final', , , 4, 2)
                                                              ^
ERROR:  syntax error at or near ","
LINE 1: ...goals, opponent_goals) VALUES(2018, 'Third Place', , , 2, 0)

问题原因 #

1. 插入新球队后未重新查询获取team_id，导致WINNER_ID/OPPONENT_ID仍为空值
2. 使用LIKE做模糊匹配易出现匹配失败，且PSQL返回结果可能包含空白字符，导致空值判断失误
3. 表头判断逻辑分散，易出现遗漏

修正后的脚本 #

if [[ $1 == "test" ]]
then
  PSQL="psql --username=postgres --dbname=worldcuptest -t --no-align -c"
else
  PSQL="psql --username=freecodecamp --dbname=worldcup -t --no-align -c"
fi

# Do not change code above this line. Use the PSQL variable above to query your database.

echo $($PSQL "TRUNCATE TABLE games, teams")

cat games.csv | while IFS="," read YEAR ROUND WINNER OPPONENT WG OG
do
  # 跳过表头行
  if [[ $YEAR == "year" ]]
  then
    continue
  fi

  # 处理获胜球队
  WINNER_ID=$($PSQL "SELECT team_id FROM teams WHERE name = '$WINNER'")
  # 去除结果中的空白字符
  WINNER_ID=$(echo $WINNER_ID | xargs)
  if [[ -z $WINNER_ID ]]
  then
    $PSQL "INSERT INTO teams (name) VALUES ('$WINNER')"
    # 插入后立即获取新的team_id
    WINNER_ID=$($PSQL "SELECT team_id FROM teams WHERE name = '$WINNER'")
    WINNER_ID=$(echo $WINNER_ID | xargs)
  fi

  # 处理对手球队
  OPPONENT_ID=$($PSQL "SELECT team_id FROM teams WHERE name = '$OPPONENT'")
  OPPONENT_ID=$(echo $OPPONENT_ID | xargs)
  if [[ -z $OPPONENT_ID ]]
  then
    $PSQL "INSERT INTO teams (name) VALUES ('$OPPONENT')"
    OPPONENT_ID=$($PSQL "SELECT team_id FROM teams WHERE name = '$OPPONENT'")
    OPPONENT_ID=$(echo $OPPONENT_ID | xargs)
  fi

  # 插入比赛数据
  ADD_DATA=$($PSQL "INSERT INTO games(year, round, winner_id, opponent_id, winner_goals, opponent_goals) VALUES($YEAR, '$ROUND', $WINNER_ID, $OPPONENT_ID, $WG, $OG)")
done

修正说明 #

• 新增表头判断，用continue直接跳过，逻辑更简洁
• 将LIKE改为=精准匹配球队名称，避免模糊匹配的不确定性
• 用xargs去除PSQL返回结果中的空白字符，确保空值判断准确
• 插入新球队后立即重新查询team_id，保证插入比赛数据时变量有有效值
• 调整代码缩进，提升可读性

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