-----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Steve Clark Sent: Thursday, January 24, 2013 11:47 AM To: pgsql Subject: noobie question Hi list, This may be really simple - I usually do it using a procedural language such as php or a bash script. 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? Thanks for your consideration. -- Stephen Clark -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general --------------------------------------------------------------------------------------------------------- I think you could do this in plpgsql, but what I see is a lot of updates that "may" not be needed. If your table has a million records, 1-1000000, and you want to insert a record between positions 1 & 2, you're basically updating the whole table. That's a lot of thrashing! I don't know what you are trying to do with this, but if what you really care about is just ordering the records, insert "1.5" (a float of course). If the id has to be an integer, maybe you could define a (materialized) view to do that. But if this table's going to have a lot of records in it, and expecially if it has indexes on it, you may want to avoid sweeping updates like this. If nothign else, it may just take a long time. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
