PGError: duplicate key

Rails
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),
Rick [email protected] wrote:

[…]
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
psql> \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

On Wed, 2010-04-14 at 21:23 +0200, Lionel B. wrote:

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

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

execute ‘ALTER SEQUENCE “numbers_pkey_seq” RESTART WITH 9;’

is probably close to what you need

Craig

–
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

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