Re: noobie question

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Steve Clark
Sent: Thursday, January 24, 2013 12:46 PM
To: Jeff Janes
Cc: Chris Angelico; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] 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

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-----------------------------------------------------------------------------------------------
delete from mytable where id = 4;
update mytable set id = id-1 where id > 4;