duplicate key violates unique constraint

rubyonrails-talk
1

I've run into an interesting issue. When setting up a table with data from a file (I'm doing this in a block). I find that I can't create separate entries manually after the import. It complains about a duplicate primary key. I've tried Schedule.id += 1 but id= either isn't defined or accessible in the class

Here is my code:

    FasterCSV.foreach("schedule_store/#{@schedule_file.filename}", "r") do |row|       unless row[3] == "CRN"

        # This would be faster as straight SQL if the current method         # slows down too much.

2

Is each row instance creating a new id and therefore starting at 1? Is each row an active record object of the same kind?

3

If I put the code in the controller rather than the model and do a save rather than create it works fine. I'd rather put this code in the model though.

Also how do you reset the id sequence back to one? I need to do this when I clear out the old data and import new data as these schedules could possibly get rather long and I don't know how long this program will be used.

4

I don't know exactly what the cause of your problem is, but I have a suspicion. In postgres, you may have a table called 'schedules' and there will also then be a table called 'schedules_id_seq'. This specifies the last id used in schedules. If your last serial id in schedules was 499, then this will be noted in schedules_id_seq. This value in schedules_id_seq needs to be adjusted to the highest id of the rows that you imported back after recreating the table, otherwise trying to insert anything with an id of less than 499 will give precisely the type of error your server[and thus you] raised.

I hope this is helpful.

David

5

Sorry,

I thought you were running a script outside of your app to add data to the database.

Maybe you can put the code in a helper and call it from the controller?

6

Thanks guys, DavidB your suggestion sounds like it might solve my problems.

Sean I probably should put it in a helper anyway.

Thanks again.