-----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Steve Clark Sent: Thursday, January 24, 2013 12:46 PM To: Jeff Janes Cc: Chris Angelico; pgsql-general@xxxxxxxxxxxxxx Subject: 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@xxxxxxxxx> wrote: >> 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'); > 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@xxxxxxxxxxxxxx) 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; -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
