Re: noobie question

On 01/24/2013 12:36 PM, Jeff Janes wrote:
> On Thu, Jan 24, 2013 at 8:53 AM, Chris Angelico <rosuav(at)gmail(dot)com> wrote:
>> 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');
> To do this reliably, you would have to set the unique constraint to
> DEFERRABLE INITIALLY DEFERRED, otherwise you will get errors due to
> transient duplicates.
>
> If his design requires that this kind of update be done regularly, he
> should probably reconsider that design.
>
> Cheers,
>
> Jeff
>
>
Thanks All,

This is for a few very small tables, less 100 records each, that a user can delete and insert records into based on the "id"
which is displayed in a php generated html screen. The tables are rarely updated and when they are updated only one person
is accessing them at a time.

I have seen several answers on inserting what about deleting?

--
Stephen Clark