# PGError: duplicate key

**URL:** <https://www.ruby-forum.com/t/pgerror-duplicate-key/186951>\
**Category:** Rails\
**Created:** [April 14, 2010, 9:11pm UTC](https://www.ruby-forum.com/t/pgerror-duplicate-key/186951 "2010-04-14T21:11:44Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![ricklach](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/47e85d/32.png) [@ricklach](https://www.ruby-forum.com/u/ricklach)\
**Post date:** [April 14, 2010, 9:11pm UTC](https://www.ruby-forum.com/t/pgerror-duplicate-key/186951/1 "2010-04-14T21:11:44Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ricklach](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/47e85d/32.png) [@ricklach](https://www.ruby-forum.com/u/ricklach)\
**Post date:** [April 14, 2010, 9:25pm UTC](https://www.ruby-forum.com/t/pgerror-duplicate-key/186951/2 "2010-04-14T21:25:12Z")

</div>

The Wed, 14 Apr 2010 12:09:57 -0700 (PDT),  
Rick [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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

---

<div class="post-metadata">

**Author:** ![ricklach](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/47e85d/32.png) [@ricklach](https://www.ruby-forum.com/u/ricklach)\
**Post date:** [April 14, 2010, 9:37pm UTC](https://www.ruby-forum.com/t/pgerror-duplicate-key/186951/3 "2010-04-14T21:37:31Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ricklach](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/47e85d/32.png) [@ricklach](https://www.ruby-forum.com/u/ricklach)\
**Post date:** [April 14, 2010, 11:02pm UTC](https://www.ruby-forum.com/t/pgerror-duplicate-key/186951/4 "2010-04-14T23:02:25Z")

</div>

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