Postgres error “ERROR: INSERT has more target columns than expressions”

Question

Whenever I run this code I get an error saying "Error %s % e INSERT has more target columns than expressions" with a ^ showing under fthg. Any idea what the issue is here, considering fthg is already defined? Many thanks!

 cur.execute("CREATE TABLE IF NOT EXISTS soccerleague.games (gameId SERIAL PRIMARY KEY, homeTeamId INTEGER, FOREIGN KEY (homeTeamId) REFERENCES soccerleague.teams(id),awayTeamId INTEGER, FOREIGN KEY (awayTeamId) REFERENCES soccerleague.teams(id), fthg INTEGER, athg INTEGER, ftr VARCHAR, refereeId INTEGER, FOREIGN KEY (refereeId) REFERENCES soccerleague.referees(id), HY INTEGER, AY INTEGER)")

data3 = (teamsDict[homeTeam], teamsDict[awayTeam], fthg, athg, ftr, refereesDict[refereeName], hy, ay)
query3 = "INSERT INTO soccerleague.games (homeTeamId, awayTeamId, fthg, athg, ftr, refereeId, hy, ay) VALUES"
query3 += "('" + str(data3) + "', '" + x + "'),"
query3 = query3[:-1] + ";"

JGH · Accepted Answer · 2017-12-04 19:04:18Z

0

The issue is not in the table creation, but when you set the data to be inserted.

The line query3 += "('" + str(data3) + "', '" + x + "')," instructs to insert the content of data3 first, then the content of x which sums to 2 arguments. The insert statement query3 = "INSERT INTO soccerleague.games (homeTeamId, awayTeamId, fthg, athg, ftr, refereeId, hy, ay) VALUES" refers to 8 arguments. To fix the issue, either supply the 8 values (some could be null) or trim down the insert statement to the 2 firsts fields only.

Even though you set data3 to contains all the values you wanted, the insert statement is writing it as a single quoted string, which maps to the 1st field only.

answered Dec 4, 2017 at 19:04

JGH

18.2k5 gold badges40 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sammmm Over a year ago

I will go with the first option considering I need the 8 values. Problem is I am already applying this considering I am supplying with 8 values as you can see it in my code. If I want to do it as a string, what's the solution?

JGH Over a year ago

Forget your ´data3´, add the values directly. It will be much easier for you to see where to put. The quotation marks or not. On top of it it will increase readability

Collectives™ on Stack Overflow

Postgres error “ERROR: INSERT has more target columns than expressions”

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related