Re: Changing ids conflicting with serial values?

Strange - I had never realized that PostgreSQL would allow you to UPDATE a
primary key value. I thought that other db's I had used (e.g. Sybase,
Oracle, SQL Server, etc.) in the past would not allow that, and you had to
DELETE, then INSERT to modify a row that needed a different primary key.

Of course, that is only for tables whose primary key meant something - no
reason to change a serial-type primary key that does not really mean
anything.

Susan

Tom Lane
<tgl(at)sss(dot)pgh(dot)pa(dot)us> To: Steven Brown <swbrown(at)ucsd(dot)edu>
Sent by: cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Changing ids conflicting with serial values?

pgsql-general-owner(at)pos |-------------------|
tgresql.org | [ ] Expand Groups |
|-------------------|

11/02/2005 06:38
PM

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

----------------------------------------------------------------------------------------------
Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------