rails import sql, column mismatch on id
I have a series of sql statements that I am reading into my db - specificially, I am seeding a table with cities and coordinates, but am a little confused as how to handle missing ID columns in sql dumps.
My migration to create the table:
After running the migration:
sqlite> PRAGMA table_info(cities_extended) 0|id|INTEGER|1||1 1|city|varchar(255)|0||0 2|state_code|varchar(255)|0||0 3|zip|integer|0||0 4|latitude|float|0||0 5|longitude|float|0||0 6|county|varchar(255)|0||0
The sql file looks something like this:
INSERT INTO `cities_extended` VALUES ('Holtsville', 'NY', '00501', '40.8152', '-73.0455', 'Suffolk'); INSERT INTO `cities_extended` VALUES ('Holtsville', 'NY', '00544', '40.8152', '-73.0455', 'Suffolk'); INSERT INTO `cities_extended` VALUES ('Adjuntas', 'PR', '00601', '18.1788', '-66.7516', 'Adjuntas');
But when I attempt to read the .sql file into my sqlite table, I get a column mismatch error:
rails db sqlite> .read ./db/data/cities_extended.sql Error: near line 41780: table cities_extended has 7 columns but 6 values were supplied Error: near line 41781: table cities_extended has 7 columns but 6 values were supplied
As you can see from the migrated table, an extra column called id was created by rails. This prevents the table from being seeded. What is the best way to satisfy the column requirements?
So i found a work around, but I’m not convinced its the best way:
:id => false allows me to bypass the requirements.
It works for my cause, but I’m not sure its the best way because there will not be any unique ID’s on any of the records.