# Postgresql duplicate key violates unique constraint

**URL:** <https://www.ruby-forum.com/t/postgresql-duplicate-key-violates-unique-constraint/59498>\
**Category:** Rails\
**Created:** [May 3, 2006, 7:10pm UTC](https://www.ruby-forum.com/t/postgresql-duplicate-key-violates-unique-constraint/59498 "2006-05-03T19:10:31Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![gui\_dos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e79b87/32.png) [@gui\_dos](https://www.ruby-forum.com/u/gui_dos)\
**Post date:** [May 3, 2006, 7:10pm UTC](https://www.ruby-forum.com/t/postgresql-duplicate-key-violates-unique-constraint/59498/1 "2006-05-03T19:10:31Z")

</div>

I have a weird problem with creating a new record when using PostgreSQL.

From inside script/console …

```
>> entity = Entity.new
>> entity.first_name = "Foo"
>> entity.last_name = "bar"
>> entity.save

```

Throws an exception due to the following SQL error: duplicate key  
violates unique constraint.

The application was working fine before and suddenly gives this  
grief. I have upgraded all my gems  
so I’m now running Rails 1.1.2 and the problem is still there.

Any ideas?

– G.

---

<div class="post-metadata">

**Author:** ![gui\_dos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e79b87/32.png) [@gui\_dos](https://www.ruby-forum.com/u/gui_dos)\
**Post date:** [May 3, 2006, 7:10pm UTC](https://www.ruby-forum.com/t/postgresql-duplicate-key-violates-unique-constraint/59498/2 "2006-05-03T19:10:33Z")

</div>

Here is the relevant portion of my schema.rb …

create\_table “entities”, :force =\> true do |t|  
t.column “type”, :string, :limit =\> 20, :default =\> “”, :null =\>  
false  
t.column “first\_name”, :string, :limit =\> 50, :default =\> “”, :null  
=\> false  
t.column “last\_name”, :string, :limit =\> 50, :default =\> “”, :null  
=\> false  
t.column “other\_name”, :string, :limit =\> 50  
t.column “postal\_address”, :string, :limit =\> 50  
t.column “house\_address”, :string, :limit =\> 50  
t.column “phone\_number”, :string, :limit =\> 20  
end

I seem to recall some problem reports on the mailing list with  
Postgres, Rails 1.1 and STI.

Could this be the cause? Any help is appreciated.

– G.

---

<div class="post-metadata">

**Author:** ![gui\_dos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e79b87/32.png) [@gui\_dos](https://www.ruby-forum.com/u/gui_dos)\
**Post date:** [May 3, 2006, 7:10pm UTC](https://www.ruby-forum.com/t/postgresql-duplicate-key-violates-unique-constraint/59498/3 "2006-05-03T19:10:34Z")

</div>

On Apr 29, 2006, at 5:56 PM, Guido S. wrote:

> Throws an exception due to the following SQL error: duplicate key  
> violates unique constraint.

Your primary key index isn’t in sync or something.

Can you login to psql and run the following?

SELECT MAX(id) FROM entities;

What is the result?

Then run…

SELECT nextval(‘entities\_id\_seq’);

This should be higher than the last result.

Is it the same or lower? If so… did you do some importing or  
restoring? (your sequence might be off)

If it’s not higher… run this to try and fix it. (run a quick  
pg\_dump first…)

SELECT setval(‘entities\_id\_seq’, (SELECT MAX(id) FROM entities)+1);

reload your app…and see if its still happening.

Good luck!

-Robby

Robby R.  
Founder & Executive Director

PLANET ARGON, LLC  
Ruby on Rails Development, Consulting & Hosting

> **[Planet Argon](https://www.planetargon.com)**
>
> Planet Argon is a Ruby on Rails development agency specializing in maintenance and support for existing Rails apps. Based in Portland, OR, Planet Argon has been a member of the Rails community since 2004.

[www.robbyonrails.com](http://www.robbyonrails.com)

+1 503 445 2457  
+1 877 55 ARGON [toll free]  
+1 815 642 4968 [fax]

---

<div class="post-metadata">

**Author:** ![gui\_dos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e79b87/32.png) [@gui\_dos](https://www.ruby-forum.com/u/gui_dos)\
**Post date:** [May 3, 2006, 7:10pm UTC](https://www.ruby-forum.com/t/postgresql-duplicate-key-violates-unique-constraint/59498/4 "2006-05-03T19:10:39Z")

</div>

Hey!

I would like to hereby place you into the category of

CERTIFIED GENIUS

You nailed it right on the head.

I modified the sequence to use the correct value and I think I should  
be good to go …

– G.

---

<div class="post-metadata">

**Author:** ![gui\_dos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e79b87/32.png) [@gui\_dos](https://www.ruby-forum.com/u/gui_dos)\
**Post date:** [May 4, 2006, 2:13pm UTC](https://www.ruby-forum.com/t/postgresql-duplicate-key-violates-unique-constraint/59498/5 "2006-05-04T14:13:10Z")

</div>

I think the requirement to manually reset the index is a bug in  
postgresql. Either in  
implementation or design. Restoring from a dump should restore the  
indexes to a working  
state. Perhaps pg\_dump has an option to write a dump file that restores  
the index? I had  
the same problem and filed a bug report at postgresql against 8.1.3.  
The database should  
not be in an inconsistent state. MySQL does not screw up it indexes  
after a restore, you  
can insert right away.

Robby’s solution worked for me. Still, we shouldn’t have to do it.

Warren S.

---

<div class="post-metadata">

**Author:** ![gui\_dos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e79b87/32.png) [@gui\_dos](https://www.ruby-forum.com/u/gui_dos)\
**Post date:** [May 3, 2006, 7:10pm UTC](https://www.ruby-forum.com/t/postgresql-duplicate-key-violates-unique-constraint/59498/6 "2006-05-03T19:10:42Z")

</div>

On Apr 29, 2006, at 6:54 PM, Guido S. wrote:

> Hey!
> 
> I would like to hereby place you into the category of
> 
> CERTIFIED GENIUS

_blushes_

> You nailed it right on the head.

I’ve come across this several times… typically due to some import  
process or something… in any event… glad that I could help. 🙂

Cheers,

- Robby

Robby R.  
Founder & Executive Director

PLANET ARGON, LLC  
Ruby on Rails Development, Consulting & Hosting

> **[Planet Argon](https://www.planetargon.com)**
>
> Planet Argon is a Ruby on Rails development agency specializing in maintenance and support for existing Rails apps. Based in Portland, OR, Planet Argon has been a member of the Rails community since 2004.

[www.robbyonrails.com](http://www.robbyonrails.com)

+1 503 445 2457  
+1 877 55 ARGON [toll free]  
+1 815 642 4968 [fax]

---

<div class="post-metadata">

**Author:** ![gui\_dos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e79b87/32.png) [@gui\_dos](https://www.ruby-forum.com/u/gui_dos)\
**Post date:** [May 4, 2006, 2:25pm UTC](https://www.ruby-forum.com/t/postgresql-duplicate-key-violates-unique-constraint/59498/7 "2006-05-04T14:25:17Z")

</div>

I took a look at the SQL generated by pg\_dump. I think the problem is  
that the sequence values are being dumped and restored … instead of  
just being ignored.

So if you do a restore, but have munged the backup file then there is  
a problem if you don’t sync the sequence bits as well.

Am sure I missed something, but that’s what is immediately apparent …

– G.

---

<div class="post-metadata">

**Author:** ![gui\_dos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e79b87/32.png) [@gui\_dos](https://www.ruby-forum.com/u/gui_dos)\
**Post date:** [May 5, 2006, 7:22pm UTC](https://www.ruby-forum.com/t/postgresql-duplicate-key-violates-unique-constraint/59498/8 "2006-05-05T19:22:52Z")

</div>

Yes, that’s what I was referring to. And the fix is so simple either  
way 🙂

– G.

---

<div class="post-metadata">

**Author:** ![gui\_dos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e79b87/32.png) [@gui\_dos](https://www.ruby-forum.com/u/gui_dos)\
**Post date:** [May 4, 2006, 3:02pm UTC](https://www.ruby-forum.com/t/postgresql-duplicate-key-violates-unique-constraint/59498/9 "2006-05-04T15:02:29Z")

</div>

If this line from the dump is what you are talking about then yes, I  
think you have  
located the bug:

SELECT pg\_catalog.setval(pg\_catalog.pg\_get\_serial\_sequence(‘line\_items’,  
‘id’), 10, true);

I guess that if the pg\_dump had written the following command it also  
would have prevented  
the problem:

REINDEX TABLE line\_items;

Warren S.

---

<div class="post-metadata">

**Author:** ![gui\_dos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e79b87/32.png) [@gui\_dos](https://www.ruby-forum.com/u/gui_dos)\
**Post date:** [August 18, 2010, 10:39pm UTC](https://www.ruby-forum.com/t/postgresql-duplicate-key-violates-unique-constraint/59498/10 "2010-08-18T22:39:25Z")

</div>

Robby R. wrote:

> On Apr 29, 2006, at 5:56 PM, Guido S. wrote:
> 
> > Throws an exception due to the following SQL error: duplicate key  
> > violates unique constraint.
> 
> Your primary key index isn’t in sync or something.

> SELECT MAX(id) FROM entities;
> 
> SELECT nextval(‘entities\_id\_seq’);
> 
> This should be higher than the last result.
> 
> If it’s not higher… run this to try and fix it. (run a quick  
> pg\_dump first…)
> 
> SELECT setval(‘entities\_id\_seq’, (SELECT MAX(id) FROM entities)+1);

Let me up this thread as it just saved my a$$!

Is this considered a bug or a feature? Because people easily get trapped  
into it!

---

<div class="post-metadata">

**Author:** ![gui\_dos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e79b87/32.png) [@gui\_dos](https://www.ruby-forum.com/u/gui_dos)\
**Post date:** [August 3, 2006, 2:22pm UTC](https://www.ruby-forum.com/t/postgresql-duplicate-key-violates-unique-constraint/59498/11 "2006-08-03T14:22:06Z")

</div>

On Apr 29, 2006, at 6:39 PM, Robby R. wrote:

> > 
> 
> pg\_dump first…)
> 
> SELECT setval(‘entities\_id\_seq’, (SELECT MAX(id) FROM entities)+1);

Fixtures have to do the same, hence:  
ActiveRecord::Base.connection.reset\_pk\_sequence!(‘entities’)

Best,  
jeremy

---

<div class="post-metadata">

**Author:** ![gui\_dos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e79b87/32.png) [@gui\_dos](https://www.ruby-forum.com/u/gui_dos)\
**Post date:** [August 18, 2010, 11:10pm UTC](https://www.ruby-forum.com/t/postgresql-duplicate-key-violates-unique-constraint/59498/12 "2010-08-18T23:10:20Z")

</div>

I ran into this issue when I backuped a database and restored it back  
into another system.

---

<div class="post-metadata">

**Author:** ![gui\_dos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e79b87/32.png) [@gui\_dos](https://www.ruby-forum.com/u/gui_dos)\
**Post date:** [August 18, 2010, 10:41pm UTC](https://www.ruby-forum.com/t/postgresql-duplicate-key-violates-unique-constraint/59498/13 "2010-08-18T22:41:45Z")

</div>

Warren S. wrote:

> I think the requirement to manually reset the index is a bug in  
> postgresql. Either in  
> implementation or design.

I’ve never needed to do that. I wonder why…would autovacuum help?

## Best,

Marnen Laibow-Koser  
[http://www.marnen.org](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![gui\_dos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e79b87/32.png) [@gui\_dos](https://www.ruby-forum.com/u/gui_dos)\
**Post date:** [August 19, 2010, 12:44am UTC](https://www.ruby-forum.com/t/postgresql-duplicate-key-violates-unique-constraint/59498/14 "2010-08-19T00:44:16Z")

</div>

> I ran into this issue when I backuped a database and restored it back  
> into another system.

You might want to look into how that database got backed up. By default  
(at least every where I’ve done it) pg\_dump will include the statements  
necessary to “reset” the sequences… that is, all my dumps have lines  
like this:

SELECT pg\_catalog.setval(‘banners\_id\_seq’, 6, true);

Might be you’re missing that or missing the permissions to set that.

-philip

---

<div class="post-metadata">

**Author:** ![gui\_dos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e79b87/32.png) [@gui\_dos](https://www.ruby-forum.com/u/gui_dos)\
**Post date:** [October 1, 2011, 4:26am UTC](https://www.ruby-forum.com/t/postgresql-duplicate-key-violates-unique-constraint/59498/15 "2011-10-01T04:26:11Z")

</div>

Robby R. wrote in post #72333:

> SELECT MAX(id) FROM entities;  
> Then run…  
> This should be higher than the last result.  
> If it’s not higher… run this to try and fix it. (run a quick  
> pg\_dump first…)  
> SELECT setval(‘entities\_id\_seq’, (SELECT MAX(id) FROM entities)+1);  
> reload your app…and see if its still happening.

I have the same problem but I don’t know how to solve it since I can’t  
relate the solution here (names of tables, etc.) to the data given by  
the OP. How do you know you need to SELECT MAX(id) FROM entities, or  
SELECT setval(‘entities\_id\_seq’, (SELECT MAX(id) FROM entities)+1);  
with the information given by the OP? I can’t see the relation and  
therefore I have no clue how I can solve my problem.

---

<div class="post-metadata">

**Author:** ![gui\_dos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e79b87/32.png) [@gui\_dos](https://www.ruby-forum.com/u/gui_dos)\
**Post date:** [January 2, 2012, 8:00am UTC](https://www.ruby-forum.com/t/postgresql-duplicate-key-violates-unique-constraint/59498/16 "2012-01-02T08:00:34Z")

</div>

Robby R. wrote in post #72333:

> On Apr 29, 2006, at 5:56 PM, Guido S. wrote:

…

> SELECT MAX(id) FROM entities;  
> SELECT nextval(‘entities\_id\_seq’);

> This should be higher than the last result.  
> SELECT setval(‘entities\_id\_seq’, (SELECT MAX(id) FROM entities)+1);

…

Thank you so much for this post!

---

<div class="post-metadata">

**Author:** ![gui\_dos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e79b87/32.png) [@gui\_dos](https://www.ruby-forum.com/u/gui_dos)\
**Post date:** [March 19, 2013, 3:40pm UTC](https://www.ruby-forum.com/t/postgresql-duplicate-key-violates-unique-constraint/59498/17 "2013-03-19T15:40:25Z")

</div>

Robby R. wrote in post #72333:

> SELECT setval(‘entities\_id\_seq’, (SELECT MAX(id) FROM entities)+1);
> 
> reload your app…and see if its still happening.
> 
> Good luck!
> 
> -Robby

I don’t code with Ruby but i registered only to say THANK YOU Robby you  
saved my day.

---

<div class="post-metadata">

**Author:** ![gui\_dos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e79b87/32.png) [@gui\_dos](https://www.ruby-forum.com/u/gui_dos)\
**Post date:** [May 8, 2015, 8:28am UTC](https://www.ruby-forum.com/t/postgresql-duplicate-key-violates-unique-constraint/59498/18 "2015-05-08T08:28:29Z")

</div>

toni t. wrote in post #1102290:

> Robby R. wrote in post #72333:
> 
> > SELECT setval(‘entities\_id\_seq’, (SELECT MAX(id) FROM entities)+1);
> > 
> > reload your app…and see if its still happening.
> > 
> > Good luck!
> > 
> > -Robby
> 
> I don’t code with Ruby but i registered only to say THANK YOU Robby you  
> saved my day.

Hi,

I am writing below statement in pgsql file in function  
endpoint\_to\_dyninfo\_func()

SELECT setval(‘endpoints\_dyninfo\_id\_seq’, (SELECT MAX(id) FROM  
endpoints\_dyninfo));

But i am gettign error if i am compiling using user msw as,  
psql -Umsw \< all\_trigger\_func.pgsql

ERROR: syntax error at or near “endpoints\_dyninfo\_id\_seq”  
LINE 22: SELECT setval(‘endpoints\_dyninfo\_id\_seq’, (SELECT MAX(…  
^  
ERROR: function endpoint\_to\_dyninfo\_func() does not exist

## Same statement i can use using below steps. 1\> Ratnesh-alt:/databases/databases/pg\_log # psql -Umsw 2\> msw=# SELECT setval(‘endpoints\_dyninfo\_id\_seq’, (SELECT MAX(id) FROM endpoints\_dyninfo)); setval

1326  
(1 row)

Please help asap.

Thanks,  
Ratnesh