On 11/23/19 3:28 PM, Blake McBride wrote:
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?
A deferrable constraint might solve the problem.
https://www.commandprompt.com/blog/postgres_deferred_primary_keys/
https://www.postgresql.org/docs/9.6/sql-altertable.html
ALTER TABLE ... ALTER CONSTRAINT ... DEFERRABLE INITIALLY DEFERRED;
--
Angular momentum makes the world go 'round.
