INSERT has more target columns than expressions

Question

I've been reading this insert statement for two days and cannot identify which extra value is being added to the insert query.The amount of columns in the table and number of columns specified in the insert query are correct so can someone please show me which extra value is being added

I'm using a python script with dictionaries to create both the create and insert statements.PostgreSQL is used for the database.

The string created by python for Create

CREATE TABLE IF NOT EXISTS AiSTestTable(newID text not null constraint AiSTestTable_pkey  primary key,aismessagesourceid int,messagechecksum text,offset0 text,utcmonth int,aismessagerepeatind int,utcyear int,latitude2s float,increment0 text,timeout0 text,aismessageid int,positionaccuracy int,latitude int,communicationstate int,utcminute int,positionfixingdevice int,utcday int,messagedatestamp timestamp,spare int,messagerssi
float,utchour int,datafile text,messagefreq float,messagechecksumresult text,messagedecoded text,longitude2s float,longitude int,utcsecond int,messagelength int,transmissioncontrol int,aismessageidtext text,raimflag int,numberofslots0 text); ALTER table AiSTestTable OWNER to postgres;

The string created by python for Insert

INSERT INTO aistesttable (newID,aismessagesourceid,messagechecksum,offset0,utcmonth,aismessagerepeatind,longitude,latitude2s,increment0,timeout0,aismessageid,positionaccuracy,latitude,communicationstate,utcminute,positionfixingdevice,utcday,messagedatestamp,spare,messagerssi,utchour,datafile,messagefreq,messagechecksumresult,messagedecoded,longitude2s,utcyear,utcsecond,messagelength,transmissioncontrol,aismessageidtext,numberofslots0,raimflag) VALUES ('1','113669999','59','0000','0','0','5834147','45.72','0000','0000','4','1','27432000','0','60','1','0','2018-07-30 09:13:37 UTC','0000','0','-48.0772018433','24','Broker','161.975344','Fail','yes','9.72357833333','0','60','37','0','Basestation report','0000','0');

This is the code to create the strings(newID is a count value)

TagString = str(d1.keys()).replace("['","").replace("']","").replace("', '",",")

TagType = str(d2).replace("': ["," ").replace("], '",",").replace("{'","").replace("}","").replace("'","").replace("]","").replace('"','').replace("[","")

newID="'"+str(c)+"'"+","

values=str(d1.values()).replace("[[","").replace("[","").replace("]","").replace(", '",",'")

commands = "CREATE TABLE IF NOT EXISTS AiSTestTable" + "(newID text not null constraint AiSTestTable_pkey  primary key,"+TagType+"); ALTER table AiSTestTable OWNER to postgres;"

insert = "INSERT INTO aistesttable (newID,"+TagString+")" + " VALUES " + "("+newID+values+");"

Values in dictionaries

d1={'aismessagesourceid': ['113669999'], 'messagechecksum': ['59'], 'offset0': ['0000'], 'utcmonth': ['0'], 'aismessagerepeatind': ['0'], 'longitude': ['5834147'], 'latitude2s': ['45.72'], 'increment0': ['0000'], 'timeout0': ['0000'], 'aismessageid': ['4'], 'positionaccuracy': ['1'], 'latitude': ['27432000'], 'communicationstate': ['0'], 'utcminute': ['60'], 'positionfixingdevice': ['1'], 'utcday': ['0'], 'messagedatestamp': ['2018-07-30 09:13:37 UTC'], 'spare': ['0000', '0'], 'messagerssi': ['-48.0772018433'], 'utchour': ['24'], 'datafile': ['Broker'], 'messagefreq': ['161.975344'], 'messagechecksumresult': ['Fail'], 'messagedecoded': ['yes'], 'longitude2s': ['9.72357833333'], 'utcyear': ['0'], 'utcsecond': ['60'], 'messagelength': ['37'], 'transmissioncontrol': ['0'], 'aismessageidtext': ['Basestation report'], 'numberofslots0': ['0000'], 'raimflag': ['0']}

d2={'aismessagesourceid': ["'int'"], 'messagechecksum': ['text'], 'offset0': ['text'], 'utcmonth': ["'int'"],
'aismessagerepeatind': ["'int'"], 'utcyear': ["'int'"], 'latitude2s': ["'float'"], 'increment0': ['text'],
'timeout0': ['text'], 'aismessageid': ["'int'"], 'positionaccuracy': ["'int'"], 'latitude': ["'int'"], 'communicationstate': ["'int'"], 'utcminute': ["'int'"], 'positionfixingdevice': ["'int'"], 'utcday': ["'int'"], 'messagedatestamp': ['timestamp'], 'spare': ["'int'"], 'messagerssi': ["'float'"], 'utchour': ["'int'"],
'datafile': ['text'], 'messagefreq': ["'float'"], 'messagechecksumresult': ['text'], 'messagedecoded': ['text'], 'longitude2s': ["'float'"], 'longitude': ["'int'"], 'utcsecond': ["'int'"], 'messagelength': ["'int'"], 'transmissioncontrol': ["'int'"], 'aismessageidtext': ['text'], 'raimflag': ["'int'"], 'numberofslots0': ['text']}

FXD · Accepted Answer · 2019-03-25 12:31:24Z

1

The error contains exactly what you need to know.
What you're doing:

INSERT INTO aistesttable (<A list of 33 column names>) VALUES (<A list of 34 values>)

You must provide just as many values as you provide column names. You have missed something when building your query.

Now for the extra value that creates, when I put column names and values in front of each other in Excel, I get the following.

From what I see, very quickly you have a mismatch (e.g. utcmonth does not look like a month, longitude and latitude contain numbers, utcminute contains 60).

Can't you use this approach to find out where the first column where the mismatch occurs (little clue: go to d1 > spare, it contains a comma)?

edited Mar 25, 2019 at 12:31

answered Mar 25, 2019 at 12:23

FXD

2,0901 gold badge10 silver badges10 bronze badges

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

1 Comment

Liam Hendricks Over a year ago

Found the issue.Spare in the dictionary contains two values: ['0000', '0']

JGH · Accepted Answer · 2019-03-25 12:32:56Z

0

There is the value 'spare': ['0000', '0'], which you try to concatenate into one by using .replace(", '",",'") , which creates the final value '000','0', interpreted as two values in the insert command.

You would need to address this case. If you want both number in a single string value, you could use .replace(", '" , ",") , which creates the final value '000,0'

answered Mar 25, 2019 at 12:32

JGH

18.2k5 gold badges40 silver badges57 bronze badges

Comments

Cristian · Accepted Answer · 2019-03-25 12:33:54Z

0

The error is in field 'spare' because the content is an array ['0000', '0'].

And when you are creating the insert String you are adding those fields.

answered Mar 25, 2019 at 12:33

Cristian

446 bronze badges

Collectives™ on Stack Overflow

INSERT has more target columns than expressions

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related