Blake McBride <blake1024@gmail.com> writes:
> 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).
Postgres only treats primary/unique keys that way if you explicitly
mark the constraint as DEFERRABLE. Otherwise, the uniqueness check is
made immediately as each row is updated, so it's very order-dependent
as to whether something like the above will work.
Note that you pay a fairly substantial performance penalty for deferring
the check, which is why it isn't the default, even though the SQL spec
says it ought to be.
This is documented in some obscure place [ ... looks around ... ]
ah, see "Non-Deferred Uniqueness Constraints" under Compatibility
in the CREATE TABLE reference page.
regards, tom lane