Обсуждение: Updating a sequential range of unique values?

How can I update a range of constrained values in order, without having to
resubmit a query for every single possiblity?

I'm trying to create a customer-specific sequence number, so that, for each
customer, the number starts at one, and continues, 1, 2, 3, 4, 5... etc. with
no values skipped. (This is necessary, as the record is used to sort values,
and the order can be changed by the customer)

Here's sample code that demonstrates my question:

create table snark (custid integer not null, custseq integer not null,
unique(custid, custseq));

insert into snark (custid, custseq) VALUES (1, 2);
insert into snark (custid, custseq) VALUES (1, 4);
insert into snark (custid, custseq) VALUES (1, 3);
insert into snark (custid, custseq) VALUES (1, 1);

begin transaction;
DELETE FROM snark WHERE custid=1 AND custseq=2;
UPDATE snark SET custseq=custseq-1 WHERE custid=1 and custseq>2;

This generates an error!
ERROR: duplicate key violates unique constraint "snark_custid_key"

I've tried putting an "order by" clause on the query:

UPDATE snark SET custseq=custseq-1 WHERE custid=1 and custseq>2
    ORDER BY custseq ASC;

But that got me nowhere. Also, I can't defer the enforcement of the
constraint, as, according to the manual, this only works for foreign keys.

Any ideas where to go from here?

-Ben
--
"The best way to predict the future is to invent it."
- XEROX PARC slogan, circa 1978

On Fri, Feb 17, 2006 at 08:07:26PM -0800, Benjamin Smith wrote:
> How can I update a range of constrained values in order, without having to
> resubmit a query for every single possiblity?
>
> I'm trying to create a customer-specific sequence number, so that, for each
> customer, the number starts at one, and continues, 1, 2, 3, 4, 5... etc. with
> no values skipped. (This is necessary, as the record is used to sort values,
> and the order can be changed by the customer)

Indeed you can't defer unique constraints. You can work around that
though, by adding say one million to each number and then changing it
to the right number. Using negative numbers works too.

Anyway, I think your problem could be solved in two steps:

update snark set custseq = custseq + 1000000;
update snark set custseq =
    (select count(*) from snark s2
     where s2.custid = snark.custid and s2.custseq <= snark.custseq);

Hope this helps,

> Here's sample code that demonstrates my question:
>
> create table snark (custid integer not null, custseq integer not null,
> unique(custid, custseq));
>
> insert into snark (custid, custseq) VALUES (1, 2);
> insert into snark (custid, custseq) VALUES (1, 4);
> insert into snark (custid, custseq) VALUES (1, 3);
> insert into snark (custid, custseq) VALUES (1, 1);

<snip>
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.