Search Postgresql Archives

Trouble incrementing a column

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Greetings,

I am using PostgreSQL 10.10.  I am having trouble incrementing a column for reasons I can't see.  It's probably some basic SQL thing.  Your help is appreciated.

create table my_table (
    listid char(36) not null,
    seq smallint not null,
    item varchar(4096),
    primary key (listid, seq)
);

insert into my_table (listid, seq) values ('abc', 1);
insert into my_table (listid, seq) values ('abc', 2);

-- the following works some of the time
update my_table set seq=seq+1;

-- the following doe not work for reasons I do not know
update my_table set seq=seq+1 where listid='abc';

What I get is a duplicate primary key.  I wouldn't think I'd get that because I'd think the whole thing is done in a transaction so that duplicate checks wouldn't be done till the end (essentially).

Is there a clean way to do this?

Thanks!

Blake McBride

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux