Re: noobie question

On Fri, Jan 25, 2013 at 3:47 AM, Steve Clark <sclark(at)netwolves(dot)com> 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