Skip site navigation (1) Skip section navigation (2)

Re: Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?

From: Nis Jørgensen <nis(at)superlativ(dot)dk>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?
Date: 2007-09-28 09:24:17
Message-ID: fdih83$lkn$1@sea.gmane.org (view raw or flat)
Thread:
Lists: pgsql-general
Alban Hertroys skrev:
> Nis Jørgensen wrote:
>> If you can't wait, you are probably better off working around the
>> problem. Standard solution is to do:
>>
>> UPDATE  master SET m2 = -m2;
>> UPDATE  master SET m2 = -m2+1;
>>
>> or something similar.
> 
> Would something like
> 
> UPDATE master set m2 = master2.m2
>   FROM (
> 	SELECT m2 +1
> 	  FROM master m
> 	 WHERE m.master_id = master.master_id
> 	 ORDER BY m2 DESC
>  ) master2
> 
> work? I think it might be faster (and possibly cause less index bloat)
> than doing two consequent updates.


I don't understand your query. I don't think you can use a correlated
subquery in that way.

Anyway, tricks like these might work. They might stop working without
warning, if the plan changes. Relying on unspecified behavior is a
recipe for trouble.

Nis



In response to

Responses

pgsql-general by date

Next:From: Stefan SchwarzerDate: 2007-09-28 09:25:44
Subject: Re: Normalized Tables & SELECT [was: Find "smallest common year"]
Previous:From: Richard HuxtonDate: 2007-09-28 09:21:20
Subject: Re: Normalized Tables & SELECT [was: Find "smallest common year"]

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group