Re: Changing ids conflicting with serial values?

Steven Brown <swbrown(at)ucsd(dot)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