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
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
The Wed, 14 Apr 2010 12:09:57 -0700 (PDT), Rick <richard.t.lloyd-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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 -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Wed, 2010-04-14 at 21:23 +0200, Lionel Bouton wrote:> The Wed, 14 Apr 2010 12:09:57 -0700 (PDT), > Rick <richard.t.lloyd-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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).---- 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. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 On Apr 14, 3:35 pm, Craig White <craigwh...-BQ75lA0ptkhBDgjK7y7TUQ@public.gmane.org> wrote:> On Wed, 2010-04-14 at 21:23 +0200, Lionel Bouton wrote: > > The Wed, 14 Apr 2010 12:09:57 -0700 (PDT), > > Rick <richard.t.ll...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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). > > ---- > 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.-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.