On Fri, Jan 25, 2013 at 3:47 AM, Steve Clark <sclark@xxxxxxxxxxxxx> wrote: > Say I have a table that has 2 columns like > create table "foo" ( > id integer not null, > name text > ); > CREATE UNIQUE INDEX "foo_pkey" on "foo" using btree ( "id" "int4_ops" ); > > with 10 rows of data where id is 1 to 10. > > Now I want to insert a new row ahead of id 5 so I have to renumber the rows > from 5 to 10 increasing each by one. > > Or the opposite I want to delete a row an renumber to close up the gap. > > Is there an easy way to do this in postgresql without resulting to some > external language? This is sounding, not like an ID, but like a "position" marker or something. It's most certainly possible; all you need is a searched update: UPDATE foo SET id=id+1 WHERE id>=5; INSERT INTO foo VALUES (5,'new item at pos 5'); Be aware that this can have nasty consequences with concurrency. I strongly recommend having your ID be autonumbered and never changed (eg 'ID SERIAL PRIMARY KEY'), and have your reordered field called 'position' or whatever makes sense; then you can select a set of records by their IDs and move them more safely. Alternatively, if these sorts of reordering operations are rare, you could just lock the whole table, but that's a major concurrency killer. (Though not as bad as simply throwing back a serialization error at the end. I was working with a system yesterday that did exactly that... along with taking, I kid you not, over 900ms to perform a single operation. So concurrency was desperately needed and not an option.) ChrisA -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
