Thread: 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Fri, Jan 25, 2013 at 3:47 AM, Steve Clark <sclark@netwolves.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'); Be aware that this can have nasty consequences with concurrency. I strongly recommend having your ID be autonumbered and never changed (eg 'ID SERIAL PRIMARY KEY'), and have your reordered field called 'position' or whatever makes sense; then you can select a set of records by their IDs and move them more safely. Alternatively, if these sorts of reordering operations are rare, you could just lock the whole table, but that's a major concurrency killer. (Though not as bad as simply throwing back a serialization error at the end. I was working with a system yesterday that did exactly that... along with taking, I kid you not, over 900ms to perform a single operation. So concurrency was desperately needed and not an option.) ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
-----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Steve Clark Sent: Thursday, January 24, 2013 11:47 AM To: pgsql Subject: [GENERAL] 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@postgresql.org) 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 millionrecords, 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 havea lot of records in it, and expecially if it has indexes on it, you may want to avoid sweeping updates like this. Ifnothign else, it may just take a long time. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 01/24/2013 08:47 AM, Steve Clark wrote:
> 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?
I can see this taking a lot of overhead as the table increases.
I guess it comes down to what you are trying to achieve?
Do you want a gapless sequence?
Do you want a ROWNUM?
Something else?
>
> Thanks for your consideration.
>
>
>
--
Adrian Klaver
adrian.klaver@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Jan 24, 2013 at 8:53 AM, Chris Angelico <rosuav@gmail.com> wrote: > On Fri, Jan 25, 2013 at 3:47 AM, Steve Clark <sclark@netwolves.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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 01/24/2013 12:36 PM, Jeff Janes wrote: > On Thu, Jan 24, 2013 at 8:53 AM, Chris Angelico <rosuav@gmail.com> wrote: >> On Fri, Jan 25, 2013 at 3:47 AM, Steve Clark <sclark@netwolves.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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
-----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Steve Clark Sent: Thursday, January 24, 2013 12:46 PM To: Jeff Janes Cc: Chris Angelico; pgsql-general@postgresql.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@gmail.com> wrote: >> On Fri, Jan 25, 2013 at 3:47 AM, Steve Clark <sclark@netwolves.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 personis 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@postgresql.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; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Fri, Jan 25, 2013 at 4:45 AM, Steve Clark <sclark@netwolves.com> wrote: > 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? Deleting works exactly the same way; you just subtract instead of adding. And thanks Jeff, I forgot about that requirement. Still, searched update is the easiest solution. However, do seriously rethink your design. At very least, the "id" field is misnamed; it's not the record's identity if it changes. If your only two operations are "insert" and "delete" (with inserts permitted at either end of the list as well as in the middle), one way you could do it is to have a serially-numbered ID, and a 'pos'. Adding to the end means inserting a row with a pos one higher than the current highest. Inserting a record before another one means inserting a row with the same pos - no renumbering needed. Deleting a row is done by its id, not its position. And when you query the table, just ask for them "ORDER BY POS, ID DESC" - this will show them in the right order. This doesn't, however, handle arbitrary reordering of records. For that, you will ultimately need to renumber the positions. ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 01/24/2013 01:06 PM, Chris Angelico wrote: > On Fri, Jan 25, 2013 at 4:45 AM, Steve Clark <sclark@netwolves.com> wrote: >> 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? > Deleting works exactly the same way; you just subtract instead of adding. > > And thanks Jeff, I forgot about that requirement. Still, searched > update is the easiest solution. > > However, do seriously rethink your design. At very least, the "id" > field is misnamed; it's not the record's identity if it changes. If > your only two operations are "insert" and "delete" (with inserts > permitted at either end of the list as well as in the middle), one way > you could do it is to have a serially-numbered ID, and a 'pos'. Adding > to the end means inserting a row with a pos one higher than the > current highest. Inserting a record before another one means inserting > a row with the same pos - no renumbering needed. Deleting a row is > done by its id, not its position. And when you query the table, just > ask for them "ORDER BY POS, ID DESC" - this will show them in the > right order. This doesn't, however, handle arbitrary reordering of > records. For that, you will ultimately need to renumber the positions. > > ChrisA > > Hi Chris, It is really called rule_num and relates to "in what order firewall rules are applied". And it used to allow the user to place the firewall rules where they want them in relation to other rules. This is an old design, of which I had no input, but am now maintaining. Like I said initially I have php, bash or C code to do the reordering and was just wondering if there was a slick way to do it without having to resort to some external mechanism. Thanks to all who responded. -- Stephen Clark
On 01/24/2013 01:06 PM, Chris Angelico wrote: > On Fri, Jan 25, 2013 at 4:45 AM, Steve Clark <sclark@netwolves.com> wrote: >> 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? > Deleting works exactly the same way; you just subtract instead of adding. > > And thanks Jeff, I forgot about that requirement. Still, searched > update is the easiest solution. > > However, do seriously rethink your design. At very least, the "id" > field is misnamed; it's not the record's identity if it changes. If > your only two operations are "insert" and "delete" (with inserts > permitted at either end of the list as well as in the middle), one way > you could do it is to have a serially-numbered ID, and a 'pos'. Adding > to the end means inserting a row with a pos one higher than the > current highest. Inserting a record before another one means inserting > a row with the same pos - no renumbering needed. Deleting a row is > done by its id, not its position. And when you query the table, just > ask for them "ORDER BY POS, ID DESC" - this will show them in the > right order. This doesn't, however, handle arbitrary reordering of > records. For that, you will ultimately need to renumber the positions. > > ChrisA > > Hi Chris, It is really called rule_num and relates to "in what order firewall rules are applied". And it used to allow the user to place the firewall rules where they want them in relation to other rules. This is an old design, of which I had no input, but am now maintaining. Like I said initially I have php, bash or C code to do the reordering and was just wondering if there was a slick way to do it without having to resort to some external mechanism. Thanks to all who responded. -- Stephen Clark -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Thu, Jan 24, 2013 at 14:03:33 -0500, Steve Clark <sclark@netwolves.com> wrote: > >It is really called rule_num and relates to "in what order firewall rules are applied". And it used >to allow the user to place the firewall rules where they want them in relation to other rules. If you just need ordering, you could choose to use a string or numeric to give you ordering. That allows you to insert values in between existing records without having to renumber. When displaying the data the application can number them based on ordering. And keep track of the current mapping between the number on the screen and the key in the database. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Fri, Jan 25, 2013 at 4:45 AM, Steve Clark <sclark@netwolves.com> wrote: > 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? Deleting works exactly the same way; you just subtract instead of adding. And thanks Jeff, I forgot about that requirement. Still, searched update is the easiest solution. However, do seriously rethink your design. At very least, the "id" field is misnamed; it's not the record's identity if it changes. If your only two operations are "insert" and "delete" (with inserts permitted at either end of the list as well as in the middle), one way you could do it is to have a serially-numbered ID, and a 'pos'. Adding to the end means inserting a row with a pos one higher than the current highest. Inserting a record before another one means inserting a row with the same pos - no renumbering needed. Deleting a row is done by its id, not its position. And when you query the table, just ask for them "ORDER BY POS, ID DESC" - this will show them in the right order. This doesn't, however, handle arbitrary reordering of records. For that, you will ultimately need to renumber the positions. ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 01/24/2013 08:47 AM, Steve Clark wrote:
> 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?
I can see this taking a lot of overhead as the table increases.
I guess it comes down to what you are trying to achieve?
Do you want a gapless sequence?
Do you want a ROWNUM?
Something else?
>
> Thanks for your consideration.
>
>
>
--
Adrian Klaver
adrian.klaver@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
-----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Steve Clark Sent: Thursday, January 24, 2013 11:47 AM To: pgsql Subject: [GENERAL] 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@postgresql.org) 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 millionrecords, 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 havea lot of records in it, and expecially if it has indexes on it, you may want to avoid sweeping updates like this. Ifnothign else, it may just take a long time. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Thu, Jan 24, 2013 at 14:03:33 -0500, Steve Clark <sclark@netwolves.com> wrote: > >It is really called rule_num and relates to "in what order firewall rules are applied". And it used >to allow the user to place the firewall rules where they want them in relation to other rules. If you just need ordering, you could choose to use a string or numeric to give you ordering. That allows you to insert values in between existing records without having to renumber. When displaying the data the application can number them based on ordering. And keep track of the current mapping between the number on the screen and the key in the database. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
-----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Steve Clark Sent: Thursday, January 24, 2013 12:46 PM To: Jeff Janes Cc: Chris Angelico; pgsql-general@postgresql.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@gmail.com> wrote: >> On Fri, Jan 25, 2013 at 3:47 AM, Steve Clark <sclark@netwolves.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 personis 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@postgresql.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; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 01/24/2013 12:36 PM, Jeff Janes wrote: > On Thu, Jan 24, 2013 at 8:53 AM, Chris Angelico <rosuav@gmail.com> wrote: >> On Fri, Jan 25, 2013 at 3:47 AM, Steve Clark <sclark@netwolves.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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Thu, Jan 24, 2013 at 8:53 AM, Chris Angelico <rosuav@gmail.com> wrote: > On Fri, Jan 25, 2013 at 3:47 AM, Steve Clark <sclark@netwolves.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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Fri, Jan 25, 2013 at 3:47 AM, Steve Clark <sclark@netwolves.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'); Be aware that this can have nasty consequences with concurrency. I strongly recommend having your ID be autonumbered and never changed (eg 'ID SERIAL PRIMARY KEY'), and have your reordered field called 'position' or whatever makes sense; then you can select a set of records by their IDs and move them more safely. Alternatively, if these sorts of reordering operations are rare, you could just lock the whole table, but that's a major concurrency killer. (Though not as bad as simply throwing back a serialization error at the end. I was working with a system yesterday that did exactly that... along with taking, I kid you not, over 900ms to perform a single operation. So concurrency was desperately needed and not an option.) ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 01/24/2013 01:06 PM, Chris Angelico wrote: > On Fri, Jan 25, 2013 at 4:45 AM, Steve Clark <sclark@netwolves.com> wrote: >> 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? > Deleting works exactly the same way; you just subtract instead of adding. > > And thanks Jeff, I forgot about that requirement. Still, searched > update is the easiest solution. > > However, do seriously rethink your design. At very least, the "id" > field is misnamed; it's not the record's identity if it changes. If > your only two operations are "insert" and "delete" (with inserts > permitted at either end of the list as well as in the middle), one way > you could do it is to have a serially-numbered ID, and a 'pos'. Adding > to the end means inserting a row with a pos one higher than the > current highest. Inserting a record before another one means inserting > a row with the same pos - no renumbering needed. Deleting a row is > done by its id, not its position. And when you query the table, just > ask for them "ORDER BY POS, ID DESC" - this will show them in the > right order. This doesn't, however, handle arbitrary reordering of > records. For that, you will ultimately need to renumber the positions. > > ChrisA > > Hi Chris, It is really called rule_num and relates to "in what order firewall rules are applied". And it used to allow the user to place the firewall rules where they want them in relation to other rules. This is an old design, of which I had no input, but am now maintaining. Like I said initially I have php, bash or C code to do the reordering and was just wondering if there was a slick way to do it without having to resort to some external mechanism. Thanks to all who responded. -- Stephen Clark -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Thu, Jan 24, 2013 at 12:03 PM, Steve Clark <sclark@netwolves.com> wrote: > On 01/24/2013 01:06 PM, Chris Angelico wrote: >> >> On Fri, Jan 25, 2013 at 4:45 AM, Steve Clark <sclark@netwolves.com> wrote: >>> >>> 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? >> >> Deleting works exactly the same way; you just subtract instead of adding. >> >> And thanks Jeff, I forgot about that requirement. Still, searched >> update is the easiest solution. >> >> However, do seriously rethink your design. At very least, the "id" >> field is misnamed; it's not the record's identity if it changes. If >> your only two operations are "insert" and "delete" (with inserts >> permitted at either end of the list as well as in the middle), one way >> you could do it is to have a serially-numbered ID, and a 'pos'. Adding >> to the end means inserting a row with a pos one higher than the >> current highest. Inserting a record before another one means inserting >> a row with the same pos - no renumbering needed. Deleting a row is >> done by its id, not its position. And when you query the table, just >> ask for them "ORDER BY POS, ID DESC" - this will show them in the >> right order. This doesn't, however, handle arbitrary reordering of >> records. For that, you will ultimately need to renumber the positions. >> >> ChrisA >> >> > Hi Chris, > > > It is really called rule_num and relates to "in what order firewall rules > are applied". And it used > to allow the user to place the firewall rules where they want them in > relation to other rules. > > This is an old design, of which I had no input, but am now maintaining. Like > I said initially I have > php, bash or C code to do the reordering and was just wondering if there was > a slick way to > do it without having to resort to some external mechanism. > > Thanks to all who responded. So do the numbers need to be a gapless sequence? if not why not have each position be, say, 10,000 apart, and just insert new ones halfway between the two nearest rules?