PGError: duplicate key

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

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

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).

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