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@xxxxxxxxxxxxx> To: Steven Brown <swbrown@xxxxxxxx>
Sent by: cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: Changing ids conflicting with serial values?
pgsql-general-owner@pos |-------------------|
tgresql.org | [ ] Expand Groups |
|-------------------|
11/02/2005 06:38
PM
Steven Brown <swbrown@xxxxxxxx> 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
----------------------------------------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
