MONETDB unexpected IDENT in: error on insert

Question

I have a table and want to insert some data from a file, but I am getting some very odd error messages, I cant understand

CREATE TABLE "MCS_HHGRID_MASTER" (
 "FAMSRNO" VARCHAR(7),
 "PNUM" INT ,
 "PSEX" SMALLINT ,
 "PDOBD" SMALLINT ,
 "PDOBM" SMALLINT ,
 "PDOBY" INT ,
 "PDIED" SMALLINT ,
 "PDODD" SMALLINT ,
 "PDODM" SMALLINT ,
 "PDODY" INT ,
 "PCOBS" VARCHAR(4) ,
 "PCOBN" INT ,
 "PETHNICC" SMALLINT ,
 "PETHNIC" SMALLINT ,
 "PETHNIC06" SMALLINT ,
 "PETHNIC08" SMALLINT ,
 "PETHNIC11" SMALLINT ,
 "PRELCM" SMALLINT ,
 "PNAME" VARCHAR(30) ,
 "PRES1" SMALLINT ,
 "PRES2" SMALLINT ,
 "PRES3" SMALLINT ,
 "PRES4" SMALLINT ,
 "PRES5" SMALLINT
 );

example data from file:

M10107X|1|2|1|1|1968|||||XE|||||||7|tom|1|1|1|1|
M10107X|2|2|1|5|1997||||||||||||11|dick|1|1|1|1|
M10107X|3|2|1|1|1995||||||||||||11|harry|1|1|1|1|
M10107X|4|1|1|3|1967||||||||||||7|mary|1|1|1|1|
M10107X|100|1|1|9|2000||||||||||||96|joanne|1|1|1|1|

mclient -u monetdb -d mcs "COPY INTO MCS_HHGRID_MASTER from STDIN" - < pathtofile/MCS_HHGRID_MASTER.csv

Error Message:

COPY INTO MCS_HHGRID_MASTER from STDIN: cannot open
syntax error, unexpected IDENT in : m10107x

Instead of reading from STDIN, did you try to read directly from your file using "COPY INTO MCS_HHGRID_MASTER from pathtofile/MCS_HHGRID_MASTER.csv" ? If so, did you get the same error ? — Nicolas Riousset

Hannes Mühleisen Hannes Mühleisen · Accepted Answer · 2016-05-19T09:39:39

This works for me with your example data:

mclient -u monetdb -d mcs -s "COPY INTO \"MCS_HHGRID_MASTER\" from STDIN NULL AS ''" - < pathtofile/MCS_HHGRID_MASTER.csv

Note the -s flag, the quoting of the table name and the NULL AS '' statement.

MONETDB unexpected IDENT in: error on insert

1 Answers