Re: Changing ids conflicting with serial values? - Mailing list pgsql-general

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@sss.pgh.pa.us>            To:       Steven Brown <swbrown@ucsd.edu>
                  
                           Sent by:                  cc:       pgsql-general@postgresql.org
                  
                                                     Subject:  Re: [GENERAL] Changing ids conflicting with serial
values?                 

                  
                      pgsql-general-owner@pos         |-------------------|
                  
                      tgresql.org                     | [ ] Expand Groups |
                  
                                                      |-------------------|
                  

                  
                           11/02/2005 06:38
                  
                      PM
                  

                  

                  




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

---------------------------(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
----------------------------------------------------------------------------------------------