From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: unique key problem on update
Date: Fri, 20 Sep 2013 17:07:55 +0100
User-Agent: KMail/1.9.10
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <201309201707.56028.gary.stainburn@ringways.co.uk>
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

Hi folks.

I've got the table and data shown below. 

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.

Ta

Gary


stainburn=# \d skills_pages
                                    Table "public.skills_pages"
   Column    |         Type          |                          Modifiers                           
-------------+-----------------------+--------------------------------------------------------------
 sp_id       | integer               | not null default 
nextval('skills_pages_sp_id_seq'::regclass)
 sp_sequence | integer               | not null
 sp_title    | character varying(80) | 
 sp_narative | text                  | 
Indexes:
    "skills_pages_pkey" PRIMARY KEY, btree (sp_id)
    "skills_pages_sequence" UNIQUE, btree (sp_sequence)

stainburn=# select * from skills_pages;
 sp_id | sp_sequence |     sp_title     | sp_narative 
-------+-------------+------------------+-------------
     1 |          10 | Departments      | 
     2 |          20 | Interest Groups  | 
     3 |          30 | Customer Focused | 
     4 |          40 | Business Roles   | 
     5 |          50 | Commercial       | 
     6 |          60 | People Oriented  | 
     7 |          70 | Engineering      | 
(7 rows)

stainburn=# update skills_pages set sp_sequence=sp_sequence+10 where 
sp_sequence >= 30;
ERROR:  duplicate key value violates unique constraint "skills_pages_sequence"
stainburn=# 


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