To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: unique key problem on update
Date: Fri, 20 Sep 2013 18:26:58 +0200
Lines: 23
Message-ID: <l1ht05$9cq$1@ger.gmane.org>
References: <201309201707.56028.gary.stainburn@ringways.co.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de;
 rv:1.8.1.21) Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666
In-Reply-To: <201309201707.56028.gary.stainburn@ringways.co.uk>
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

Gary Stainburn wrote on 20.09.2013 18:07:
> I want to add a new page after page 2 so I try to increase the sequence number
> of each row from page 3 onwards to make space in the sequence for the new
> record. However, I get duplicate key errors when I try. Can anyone suggest
> how I get round this.
>
> Also, the final version will be put onto a WordPress web site which means I
> will have to port it to MYSQL which I don't know, so any solution that will
> work with both systems would be a great help.
>

You need to define the primary key as deferrable:

create table skills_pages
(
  sp_id        serial not null,
  sp_sequence  integer not null,
  sp_title     character varying(80),
  sp_narative  text,
  primary key (sp_id) deferrable
);


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql