Re: Changing ids conflicting with serial values?

Steven Brown <swbrown@ucsd.edu> writes:
> When I change an id (primary key serial) in a table, the next value
> returned by the sequence for the id can conflict with that id (e.g.,
> change the id to be id + 1).  MySQL seems to handle this transparently
> by skipping conflicting values, but with PostgreSQL I get primary key
> conflicts.  It seems rather bad if a user can modify an id in a row and
> cause failures for all future inserts - it's just too fragile.  What's
> the proper way to handle this in PostgreSQL?

Plan A: don't do that.  Why in the world is it a good idea to modify an
artificial primary key?  It's not like there's some external meaning to
the values.

Plan B: after you do it, adjust the sequence generator with setval().
You can use max() to figure out where to set the generator.

            regards, tom lane