PGError: duplicate key

rubyonrails-talk
1

I'm running a rails 2.3.5 / ruby 1.8.7 / postgresql 8.4.2 app and have just run into a curious problem.

I have several tables that are initialized with data from csv files and I've noticed that, when I try to add new data to any of these tables via Table.new I get a PGError on duplicate key.

For example:

MIGRATION: class CreateNumbers < ActiveRecord::Migration   def self.up     create_table :numbers do |t|       t.string :name       t.integer :value

      t.timestamps     end     csv_file = "#{Rails.root}/db/migrate/numbers.csv"     fields = '(name, number)'     execute "COPY numbers from '#{csv_file}' DELIMITERS ',' CSV;"   end

  def self.down     drop_table :numbers   end end

CSVFILE: 1, "one", 1, Wed Apr 14 14:52:22 -0400 2010, Wed Apr 14 14:52:22 -0400 2010 2, "two", 2, Wed Apr 14 14:52:22 -0400 2010, Wed Apr 14 14:52:22 -0400 2010 3, "three", 3, Wed Apr 14 14:52:22 -0400 2010, Wed Apr 14 14:52:22 -0400 2010 4, "four", 4, Wed Apr 14 14:52:22 -0400 2010, Wed Apr 14 14:52:22 -0400 2010 5, "five", 5, Wed Apr 14 14:52:22 -0400 2010, Wed Apr 14 14:52:22 -0400 2010 6, "six", 6, Wed Apr 14 14:52:22 -0400 2010, Wed Apr 14 14:52:22 -0400 2010 7, "seven", 7, Wed Apr 14 14:52:22 -0400 2010, Wed Apr 14 14:52:22 -0400 2010 8, "eight", 8, Wed Apr 14 14:52:22 -0400 2010, Wed Apr 14 14:52:22 -0400 2010

Running db:migrate leaves me with a consistant Numbers table that I can interract with through scripts/console except I can't Number.create or n.save!.

Loading development environment (Rails 2.3.5)

Number.all.length

=> 8

Number.last

=> #<Number id: 8, name: " eight", value: 8, created_at: "2010-04-14 14:52:22", updated_at: "2010-04-14 14:52:22">

n = Number.new(:name => "nine", :value => 8)

=> #<Number id: nil, name: "nine", value: 8, created_at: nil, updated_at: nil>

n

=> #<Number id: nil, name: "nine", value: 8, created_at: nil, updated_at: nil>

n.save

ActiveRecord::StatementInvalid: PGError: ERROR: duplicate key value violates unique constraint "numbers_pkey" : INSERT INTO "numbers" ("name", "created_at", "updated_at", "value") VALUES(E'nine', '2010-04-14 19:07:44.694130', '2010-04-14 19:07:44.694130', 8) RETURNING "id"

Any ideas?

thanks in advance Rick

2

The Wed, 14 Apr 2010 12:09:57 -0700 (PDT),

[...] ActiveRecord::StatementInvalid: PGError: ERROR: duplicate key value violates unique constraint "numbers_pkey" : INSERT INTO "numbers" ("name", "created_at", "updated_at", "value") VALUES(E'nine', '2010-04-14 19:07:44.694130', '2010-04-14 19:07:44.694130', 8) RETURNING "id"

Any ideas?

You used COPY to insert data without adjusting the sequence used to initialize the id column value.

As ActiveRecord relies on the DB to fill the id column for you, the DB calls nextval() on the sequence, gets 1 which is already used.

See

\d numbers

for the name of the sequence used for the id column and lookup PostgreSQL doc for how to change the value it stores (sorry I don't remember the exact SQL syntax).

Lionel

3

The Wed, 14 Apr 2010 12:09:57 -0700 (PDT),

> [...] > ActiveRecord::StatementInvalid: PGError: ERROR: duplicate key value > violates unique constraint "numbers_pkey" > : INSERT INTO "numbers" ("name", "created_at", "updated_at", "value") > VALUES(E'nine', '2010-04-14 19:07:44.694130', '2010-04-14 > 19:07:44.694130', 8) RETURNING "id" > > > Any ideas? >

You used COPY to insert data without adjusting the sequence used to initialize the id column value.

As ActiveRecord relies on the DB to fill the id column for you, the DB calls nextval() on the sequence, gets 1 which is already used.

See > \d numbers for the name of the sequence used for the id column and lookup PostgreSQL doc for how to change the value it stores (sorry I don't remember the exact SQL syntax).

4

The naming convention for the sequence is TABLE_COLUMN_seq, so the actual line needed was:

execute "ALTER SEQUENCE numbers_id_seq RESTART WITH 9;"

ignoring that there are plugins to convert numbers to text and that populating via migrations is maybe not the best idea...

What is the best idea?

thanks again, Rick