Re: postgresql duplicate key violates unique constraint

[Fernando Perez]

Robby Russell wrote:
> On Apr 29, 2006, at 5:56 PM, Guido Sohne 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!
--
Posted via http://www.ruby-forum.com/.

[Marnen Laibow-Koser]

Warren Seltzer 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
mar...@marnen.org

[Fernando Perez]

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

[Philip Hallstrom]

> 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

[Angel M.]

Robby Russell 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.

[Nurlan A.]

Robby Russell wrote in post #72333:

> On Apr 29, 2006, at 5:56 PM, Guido Sohne 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 fro this post!

[toni t.]

Robby Russell 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.

[Ratnesh Bharti]

toni t. wrote in post #1102290:

> Robby Russell 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 pqsl 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