Worldcup database problem at unique constraint

Backend Development
1

Hi everyone. I hope someone can help me with these errors. I can’t see why my data won’t get put into my tables. I’m completely out of ideas and web searches.

duplicate error
duplicate error710×587 23.2 KB

Here is my shell script in case someone can see what I’m doing wrong so far. I have given my script executable permissions and cannot get past this error. Any help would be greatly appreciated.

#! /bin/bash

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 WINNER_GOALS OPPONENT_GOALS
do

# exclude the first row of column identifiers
if [[ $YEAR != 'year' ]]
then 
  
  # get the winner id
  WINNER_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$WINNER'")
  
  # check if the winner id is empty
  if [[ -z $WINNER_ID ]]
  then
      # if winner id is empty insert winner in the teams table
      echo "$($PSQL "INSERT INTO teams(name) VALUES('$WINNER')")"
  fi
      # opponent id
      OPPONENT_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$OPPONENT'")

      # is the opponent null
      if [[ -z $OPPONENT_ID ]]
      then 
          # insert the opponent into the teams table
          echo "$($PSQL "INSERT INTO teams(name) VALUES('OPPONENT')")"
           # opponent id as a foreign key in the games table
           OPPONENT_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$OPPONENT'")
      fi

      # insert all the data at once into the games table
      echo "$($PSQL "INSERT INTO games(year, round, winner, opponent, winner_goals, opponent_goals, winner_id, opponent_id) VALUES($YEAR, '$ROUND', $WINNER, $OPPONENT, $WINNER_GOALS, $OPPONENT_GOALS, $WINNER_ID, $OPPONENT_ID)")"

   fi         

  # winner id for the games table




done
2

The first echo command inserts the string ‘OPPONENT’ which I don’t think is what you’re trying to do.

The second echo command is trying to insert non-string values into VARCHAR fields because you haven’t included quote marks where you need to.

That is what is causing that stream of errors.

1 Like
3

Hey. Apparently im not the brightness and i found a lieu of errors (i have solved it but have to delete and redue due to the terminal not reading the worlcup database). Thank you so much and i love your handle. Very apt so far. >_<