agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: unique key problem on update
Date: Fri, 20 Sep 2013 17:07:55 +0100
Message-ID: <201309201707.56028.gary.stainburn@ringways.co.uk> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
view thread (4+ messages) latest in thread
Message-ID: <201309201707.56028.gary.stainburn@ringways.co.uk>
Permalink: ../201309201707.56028.gary.stainburn@ringways.co.uk/
Also on: postgresql.org/message-id/201309201707.56028.gary.stainburn@ringways.co.uk
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: unique key problem on update
In-Reply-To: <201309201707.56028.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox