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
