From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Trouble incrementing a column |
Date: | 2019-11-23 21:42:53 |
Message-ID: | 8273132a-8680-6cd5-e274-b42ff3066b87@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-11-23 21:47:25 | Re: Trouble incrementing a column |
Previous Message | Blake McBride | 2019-11-23 21:28:37 | Trouble incrementing a column |